OfficeFrustration - View Single Post

**David W. Fenton**

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.

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.

Natural keys are great for tables with a single-column natural PK.

Otherwise, surrogate keys make building an application substantially
easier.

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.

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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/