David W. Fenton wrote:
wrote in
m:
If the users only access the tables through forms, conforming to
best practices in Access, how are they going to get garbage into
the tables?
What if there's more than one application built on top of the
database?
I, too, agree that one should put as much of the data logic in the
back end as possible.
However, that doesn't mean I use natural keys very often. I'm
definitely opposed to compound keys for any table whose PK will be a
foreign key in another table. It causes myriad problems of all sorts
(been there, done that), and despite its being theoretically
correct, just doesn't work well in practice.
Reference is one issue that increases the tradeoff importance of
simplicity relative to the other design criteria.
Just consider one scenario:
You need to build criteria for a query-by-form interface. That means
that to query on the PK of a table with a compound PK, you end up
needing to have multiple fields in your WHERE clause. And if you're
querying multiple records in the table with the compound PK, you'll
need a complex nested OR in your WHERE clause.
I know perfectly well that theoretically speaking you're not
supposed to let your application drive the design of your schema,
but this is a case where common sense tells me that following theory
leads to enormously difficult application logic problems.
I have yet to see any evidence from you to suggest you know the first
thing about theory. Perhaps you should strive to learn a little more
about it before blathering on about it.
Natural keys are great for tables with a single-column natural PK.
A natural key is neither more nor less than a familiar surrogate.
Otherwise, surrogate keys make building an application substantially
easier.
I disagree. Since all keys are fundamentally the same thing, surrogacy
is irrelevant. The design criteria for keys a uniqueness,
irreducibility, simplicity, stability and familiarity (in no particular
order.)
And, BTW, I would, of course, advocate that any natural key that is
not used as the PK should naturally have a unique index on it.
You confuse physical and logical issues. One should declare all logical
constraints regardless of the indexes used.
And any natural key that can't have a unique index (because some
fields need to be Null) was never a candidate for PK in the first
place, and would have to have had uniqueness enforced in the
application in some fashion anyway.
One would have to be an idiot to design anything allowing NULL in the
first place.