Re: Separate PK in Jxn Tbl?

CDMAPoster_at_fortunejames.com wrote in
news:db4e7666-f9ef-4c72-a483-f951b80c6183_at_k39g2000hsf.googlegroups.co 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/