Dear Randy:
It sound like you may be aware that the problem of importing (you're close
to replication here) a table that uses a surrogate key (ID) as PK and in a
relationship is a problem. You cannot maintain the ID values, as each of
the 10 sources may use the same ID for different rows.
There is another problem that may be hidden at the moment. Whether you
acknowledge it or not, for your rows to have an identity (in the general,
philosophical sense) there must be a unique natural key as well. It might
be a person's name, or social security number (common, but not foolproof
identies) which you may need to maintain as unique.
Consider what happens when your application allows a user to create a new
dependent row (record) in a table on the many side of a one-to-many
relationship. This must uniquely reference the related foreign key table
row. How do you select this? Does the user enter the ID integer value of
the foreign table? Not likely, I'd say. You probably provide a combo box
listing all the natural key names from which they can select, probably
hiding the actual ID key value. But what if the natural key is duplicated?
How can a user reliably select between rows that appear identical in the
combo box? Of course, he cannot. So, on the "one" side of a one-to-many
relationship, there needs to be a unique natural key value for each row,
whether you use an ID for the relationship or not.
So, before you create a corrupted database that makes such operations
impossible to do reliably, it is best to consider what natural keys are
naturally unique.
It is entirely possible, I would guess, that the same customer could open an
account at 2 of the 10 locations from which you import. You need to be able
to identify that, determine whether the customer is the same in both cases,
and not insert the new account until you know whether it is really one
account or two, and have reconciled the differences. Consider this:
Jon West
1 W Second St
Watertown, PA
Jonathan West
One W 2nd St
Watertown, Pennsylvania
Are these identical? No! Not by any test the computer is likely to do for
you. Is this the same person? Probably, but it may be father and son,
living together. You may have to call to find out. But you may really need
to know which it is before posting invoices, because if it is father and
son, you need to provide two statements keeping every purchase and payment
separate. Not a simple thing to do!
These are some of the classic points in doing this. The whole thing is
treated in what might be a long chapter or two in books on the subject. I
just wanted your thinking to begin to consider things like natural key
values and keeping the correct related values together and related to the
same, correct foreign table row.
It is natural that the PK ID would be your first concern, as that is an
important mechanism in your situation. But you can have all your IDs lined
up perfectly, and still not keep the two Jons separate, distinct, and
appropriately connected to their own payments and purchases (I'm using that
as an example - you don't actually say whether you're doing this kind of
accounting, or what the application is like, but the principles are pretty
much the same).
In addition, consider that you may need to replicate the accounts, and maybe
the sales and payments, back to your 10 locations. What if a client who was
new at location A two months ago suddenly appears at location B today. Do
you want them to add a new account for this guy, because he isn't in their
system? Another likely occurrance.
Enough for now, I expect. This is the general outline. You need to fill in
details, make very infromed decisions, and have a foolproof design
(recommend testing it against eventualities before going live!) or you're
going to be quite sorry.
Tom Ellison
