Re: Separate PK in Jxn Tbl?
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