Re: Primary Key Theory Question

From: Ben <BenNOSPAM_at_NOSPAMknieff2.com>
Date: Sun, 28 Mar 2004 21:53:07 GMT
Message-ID: <7PH9c.3723$z%1.1484@twister.rdc-kc.rr.com>

Thank you for your thoughts. My comments are in line.

Leandro GuimarÃ£es Faria Corsetti Dutra wrote:

> Em Sun, 28 Mar 2004 18:56:03 +0000, Ben escreveu:
>
>

>>I have decided to do the
>>design work as 'properly' as possible. So, I've been reading through posts
>>in this group for most of a day. While I wait for Celko's books to arrive,
>>I thought I'd put a question to the gurus here.

>
>
> For doing things properly, I do think Date to be a better source.

Another good resource, now on my list. I love seeing my bookshelf sag under the weight

>>I've come to a conceptual impasse regarding primary keys. In doing my pen
>>and paper design work and I've come up with two issues: Some of my
>>entities can only be uniquely identified by all of their properties and
>>some of my entities have good candidate keys, but in real life, that data
>>might not be known.

>
>
> The rule of thumb seems to be, use a natural key when it is
> practical to do so, say there is at least one composed by two or three
> field. Over that, use a surrogate key, but do keep the natural key(s)
> as well.
>
> In fact every candidate key should be declared and enforced,
> the primary key distiction among candidate keys is pretty much
> arbitrary, needed because of practicality in SQL or general DB
> administration and usage, not because of conceptual considerations.

If I understand what you are saying here, even if I can't use SSN as the primary key, I should still create a unique index on it. I get that entirely.

The rule of thumb seems good, but the question then becomes _how_ does one generate a surrogate key that is a) unique and b) usable from an application programmer's point of view. In my wandering I read numerous options that the Sybase/MS Identity column is not good design, and I assume sequences in Oracle would be considered with about the same disdain. I'm no stranger to issues with Identity columns, and I think I understand the theoretical reason why they are not 'correct.' But sequences and Identity columns are great from an application developers point of view because you have easy access to the key of a newly created record. I'm not asking this question out of ignorance, I know I could select the max value of the column + 1, but this method *seems* to bring up whole new worries about concurrency and actually using the DB from a program.

Thanks again for your thoughts.

BK Received on Sun Mar 28 2004 - 15:53:07 CST