Append master table and update child at same time?

[Darin]

This is something that on the surface seems like it should be simple,
but I can't think of a way to do this. I have a table that is a list
of "jobs", which users create and use. It has a single autonumber key
field. Within a job, there can be multiple orders. Logically, there'd
be an orders table that is a child of the job table. This orders table
has a long integer field that matches it to the master job autonumber
field. Unfortunately, we don't always have control over orders, and
there can often be cases where orders are created that don't yet have a
master job record in our local database. I import data from HQ that
relates to these orders. That data has it's own unique identifiers,
and also contains some job-level info. I can import this data into our
local orders table, so we always have a local order record that relates
to HQ's orders. What I would LIKE to do is append a new job record for
every incoming order record that does not yet have a matching job, and
of course populate the matching long integer field in the orders table
to make it relate to the master record it just caused to be created.
It's easy to append new job records for the "orphaned" orders that are
imported. But since the key field of the parent table is an
autonumber, I don't know how to relate that back to the child table so
that is no longer orphaned.

Does that make sense?

 

[Tom Ellison]

Dear Darin:

Perhaps a simpler approach. Put all the "orphaned" dependent table rows
under a SINGLE Job record that is "unassigned". You'll be able to find all
of them very readily that way, and reassign the long integer link to the
proper Job after you have created it.

If you make the "name" of this account "*** unassigned ***" then you can
always find this special record by that key.

By the way, the Name column of jobs should probably be uniquely indexed.
You wouldn't want a combo box list of Jobs to contain duplicates, would you?
Uniqueness of natural keys is not obviated by the use of autonumber keys.

Now, if you have uniqueness of both the autonumber key and the natural
"name" key, which should be the primary key for the table? Well, the table
will be physically ordered according the the PK when the database is
compacted. So, for which of the keys is such ordering most useful? Well,
how often will you list the Jobs by the autonumber? Wouldn't you always
list them alphabetically by the "name"? So, the "name" would be a good
choice for PK, with the autonumber being a separate unique index. It still
works for relationships that way.

We're almost there. Why even have the autonumber column? If you use the PK
"name" column in the dependent tables what is the penalty? A bit of disk
space, and maybe one extra level in the BTree for the lookup in the index.
What is the gain? You save disk space (eliminating a column in the foreign
table) and, when all you need is the Job name, you won't even need to JOIN
to the Job table to get it. It's already in the dependent table. It's
FASTER!

Not a popular option, but it works perfectly. It isn't always clear which
is faster.

Tom Ellison

 

[google]

Thanks for the reply Tom! Unfortunately, there are several pitfalls,
most of which boil down to the fact that I don't have complete control
over the data. I'm dealing with a lot of existing data... tens of
thousands of records. Many of which do have duplicate names. But for
the sake of just discussing the concept, I have to be honest: the
thought of keying off of a name field does not make me feel good! In a
database with many child tables, repeating a 50 character text field in
each record of every table seems a bit inefficient. I'm also a bit
uneasy at the thought of keying and linking on editable fields. In our
business, there are very valid reasons to have to update the job name.
I know cascading updates should take care of that, but that just
doesn't "feel" as robust as keying off of a reliably unique field that
can't be modified. Maybe I'm just old-fashioned! ;-)

 

[Tom Ellison]

Dear Darin:

I've heard all this before. Actually, after 25 years in database business,
I'd say that identity keys are very new-fashioned. But I'm not going to try
to make a convert of you. However, if you make the effort to try this and
see how it affects your work, I'm telling you you'll see something quite
different from what you expect.

Tom Ellison