Re: Stupid Database Tricks


"Marshall" <marshall.spight@xxxxxxxxx> wrote in message
news:1179892760.282700.315160@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On May 22, 1:48 pm, Bob Badour <bbad...@xxxxxxxxxxxxxxxx> 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.


.

Relevant Pages

  • Re: M:M/Multiple Keys
    ... the SyncService - you'll see that things like the value of a primary key is ... keys is that while your design become deeper and deeper, ... database itself but in the interface as well. ... I stopped using composite keys many years ago and since then, ...
    (microsoft.public.access.modulesdaovba)
  • C# programmer looking for a job
    ... Software Development including Desktop, Client/Server and Database ... Practical skills in object oriented design and design patterns ... XML, Oracle, CVS, VSS, Delphi, bug tracking. ... Developed in Delphi5; ...
    (misc.immigration.usa)
  • Re: Key Violations in Append Queries
    ... Normally, if a primary key or other unique index is created, it is because ... the design of the database requires uniqueness for the key involved. ... Whoever designed the database supposedly felt this was necessary. ...
    (microsoft.public.access.queries)
  • Re: problem using identity column as primary key
    ... >> I am thinking of creating an identity column to use it as primary key ... More and more programmers who have absolutely no database training are ... the gap in the sequence is not filled in and the sequence ... vin CHARNOT NULL REFERENCES Motorpool); ...
    (microsoft.public.sqlserver.programming)
  • Re: O/R Mapper
    ... | - create E/R model from niam model ... classes that contain, not only data, but also functionality as OO design is ... a database where they do not exist in the object model is corrupting the ...
    (microsoft.public.dotnet.languages.csharp)