Re: Separate PK in Jxn Tbl?

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sun, 27 Jan 2008 18:14:24 -0400
Message-ID: <479d0246$0$4072$9a566e8b_at_news.aliant.net>


David W. Fenton wrote:

> 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.

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 are: 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. Received on Sun Jan 27 2008 - 23:14:24 CET

Original text of this message