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
"Darin" <go****@darincline.com> wrote in message
news:11********************@z34g2000cwc.googlegrou ps.com...
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?
