Board index » delphi » Changing Key Fields Without Orphaning Linked Records

Changing Key Fields Without Orphaning Linked Records

This is probably a simple question.  I have a form with multiples tables
in a One-Many relationship.  My question is how do I handle users
changing the value in the Key Field in the Master record without
orphaning records in the Detail tables?

Thanks in Advance,

Dean.

 

Re:Changing Key Fields Without Orphaning Linked Records


Quote
In article <30FCD673.2...@gold.tc.umn.edu>, Dean Millam (cull0...@gold.tc.umn.edu) writes:
>This is probably a simple question.  I have a form with multiples tables
>in a One-Many relationship.  My question is how do I handle users
>changing the value in the Key Field in the Master record without
>orphaning records in the Detail tables?

>Thanks in Advance,

>Dean.

This is a common problem when user-data fields are used as primary
keys.

The best solution is to add a simple unique numeric field to each
table, using that field as the key. Then, don't ever show it to
your user, since there is no information in it they would need to
change.

This is the one trick they never show you when they talk about
'normalization' of databases. Your primary keys should carry no
information in them, that way you never have to deal with updates
and the havoc updates wreak on one-many and many-many
relationships. Don't ever be tempted by a user telling you that such
and such piece of data is unique or will never change. *never* is
supposed to be a very long time, but usually lasts only until beta
testing, if that long.

Adding the extra field won't have any impact on the users' data or
operations, since they never see it. It *does*, however, provide
for simplified development and maintenance, which translates to
less coded machinery, which means greater robustness in the
application.

If you'd like some more info, or ideas relating to your specific db,
e-mail me your schema and I'd be glad to give you a hand.

HTH
Chris

--
Chris Hanagan                   A society that will trade a
cbhan...@cbh.win.net            little liberty for a little
Key West, Florida               order will deserve neither
                                and lose both.
                                (Thomas Jefferson)

Other Threads