Re: Stupid Database Tricks

"Roy Hann" <specially_at_processed.almost.meat> wrote in message news: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

In my experience, the mind-set of developers who auto-id all tables causes them to avoid identifying or enforcing appropriate natural keys (whether through ignorance or malice, I would hesistate to say). Consequently, there will be patients with more than one patient ID. So now the application must provide a way to merge them; I submit that this is a much more difficult task. Received on Wed May 23 2007 - 19:23:44 CEST