Re: Stupid Database Tricks
Date: Wed, 23 May 2007 11:51:53 +0100
Message-ID: <bqKdnc-IK4tXgcnbRVnyiAA_at_pipex.net>
"David Cressey" <cressey73_at_verizon.net> wrote in message
news:E5U4i.13768$mD.8066_at_trndny02...
>
> "Marshall" <marshall.spight_at_gmail.com> wrote in message
> news:1179892760.282700.315160_at_q66g2000hsg.googlegroups.com...
> Now let me answer the question, "who wouldn't"? Someone who has learned
> database design before building their first Access database, that's who.
Actually, although I obviously agree that row numbers are spurious, I think you are mistaken about who wouldn't use them, and unless you meet it head-on you will lose the argument every time.
The most compelling argument in favour of arbitrary row numbers being used as keys and foreign keys is that they can be used to avoid propagating user-entered values through multiple tables. If I allow a user to key in a patient ID number say, and then propagate that key to dozens or hundreds of other test and treatment tables, then I also have to provide application code to reliably and completely propagate any subsequent correction to all those tables too. But if I record the patient ID in just one table and map it to a system-generated number, and use the system-generated value as the key and foreign keys in all other tables, I would only need to make the correction in one place.
The proper response to this sillines is to point out that in SQL when one declares a foreign key constraint, it should be declared ON UPDATE CASCADE. Then all the problems go away.
Roy Received on Wed May 23 2007 - 12:51:53 CEST