Re: Simple database layout - Am I on the right track?

"rkc" <rkc@xxxxxxxxx> wrote

What about the "rule" that the ubiquitous
"most of us" agree with that every table
should have an autonumber "primary key"?

I think my statement about "most of us recognizing levels of normalization"
is accurate with regard to "most of the people providing accurate answers to
questions in this newsgroup". I know very few, if any, people, who believe
that _every table_ should have an _autonumber primary key_.

Autonumbers (and their equivalent under other names in other RDBs) are handy
and useful in many instances. They are, IMNSHO, unsuited for some other
uses, such as, being displayed to a user -- I have seen too many times when
someone chose to use an Autonumber as an identifier and the bookkeeper,
accountant, or boss asked "what happened to the other [whatever was so
identified]s?" when all that had happened was that the sequence of
Autonumber identifiers was not monotonically increasing.

There are certainly two sides, who seem unlikely to ever come to agreement,
to the argument over whether an Autonumber surrogate key is "better" than
joining on multi-part composite keys. Just from a long knowledge of
programming techniques, I tend to think that, from a performance
perspective, the multi-part composite keys will be slower. But, as we say
down in East Texas, "I don't have a dog in that fight."

If that isn't meaningful to you, I can restate "Multi-part composite keys
can be satisfying to the 'purist'; surrogate keys can be useful for the
'pragmatist'; I don't hold strong views that either is "right" or "better",
so they will have to argue it out amongst themselves." And, even further,
"You aren't about to lure me into that fruitless argument."

Larry Linson
Microsoft Office Access MVP


.

Relevant Pages

  • Re: Autonum field for Relationships and Replication
    ... restrict entries in candidate keys that could have been the PK?" ... autonumber field and a third with the autonumber foreign fields from the ... > CandidateID sequential in the replicated environment. ... > Replica B. Therefore creating duplicates. ...
    (microsoft.public.access.replication)
  • Re: Data Primary key vs. Artificial (Autonumber) primary key
    ... Presumably, if there are candidate keys available, they are what the user ... sequence by its primary key whenever the database is compacted. ... the clustering has no impact ... The main issue I have with the 'Autonumber PK' movement is that the ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Data Primary key vs. Artificial (Autonumber) primary key
    ... "natural" keys, I use unique indexes. ... the message about having table constraints on the candidate keys ... but I would wager than most users in the 'Autonumber PK' camp put ... worse) with multi-column natural keys (I have no objection on ...
    (microsoft.public.access.tablesdbdesign)
  • Re: How can you create a field that is concatenated?
    ... keys in relational theory, ... PRIMARY KEY in the SQL language, ... Autonumber in the Jet product. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Choosing a surrogate key vs. a composite key - "holy war" issue or clear best practice?
    ... generated surrogate keys. ... practice on choosing a surrogate key over a composite key--or if this ... Some pros to Composite primary keys: ... surrogate key, yet redundant rows may still creep into your table. ...
    (comp.databases.oracle.server)