Re: Stupid Database Tricks

"Marshall" <marshall.spight_at_gmail.com> wrote in message news:1179892760.282700.315160_at_q66g2000hsg.googlegroups.com...
> On May 22, 1:48 pm, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
> >
> > Every table will have a numeric id column, and this column will be the
> > primary key.
>
> I don't know why, exactly, since it's not like it's the worst one
> mentioned, but that one DRIVES ME CRAZY!

Drives me crazy, too. I'm not sure why. Maybe it's because it's such persistent mythology. I may have discovered at least one origin of this bad design habit.

My latest interest is, of all things, MS Access. And I'm starting out with Access 97. It turns out that, if you build a database with one of the database wizards, this is usually what you get.

Also, if you design your own database and create your own tables, there comes a time, for each table, where MS Access intervenes, and tells you that you haven't assigned a primary key, and that one is recommended, and that Access will do it for you, if that's ok.

If you say "yes" (who wouldn't?) what you get is precisely this: an extra numeric column, auto assigned, and declared as the key of the new table.

Actually, I can live with this, for so called "entity tables". It would be better for the newbie DBD to discover a natural key, and declare that, but it's not so bad. For "relationship tables", the primary key ought to be a compound key made up of two or more foreign keys. But that isn't what Access does.

Now let me answer the question, "who wouldn't"? Someone who has learned database design before building their first Access database, that's who. But Access makes it seductively simple to design and build your own database, without even a minimal understanding of the consequences of your design choices.

There are probably other tools, besides Access, that help to propagate this bad design habit. But, of them all, Access is probably pretty widespread. Received on Wed May 23 2007 - 11:48:20 CEST