Re: Primary Key Theory Question

Thank you for your thoughtful reply. I read one of Joe Celko's comments regarding using a hash function to create a key, and I thought, wow, great idea. Then I thought, a hash based on what? The initial values, a hash that gets updated (no), a hash of the x most significant columns?

You also brought up a great point about SSN, in the work that I am doing, the SSN not only could be duplicated, but it is in fact likely.

The select max()+1 route came to mind, but I don't like the concurrency/locking issues it brings up. A table lock for an insert just seems wrong to me.

So, if sequential, server generated surrogate keys are not inherently evil, why do I see so much negative press given to the Sybase/MS style Identity column? Outside of it being non-portable/non-standard, I can get the same functionality in MS and Oracle (my primary RDBMSs). Sure, I could replicate the functionality of a Oracle sequence for both using tables with one row and one field for each ID I need, and I could code up some handy SPs to make the coding easy, but, if the server will do it for me, why bother (I take laziness as the sign of a good coder in general).

<snip>
 > other databases have similar mechanisms (except for MS-Access, which  > isn't really a database...)

Too true about Access, and one of most painful aspects of my professional existence is that I need to always keep it in mind because some clients are still using it as a back end. I'm going to use your words next time I suggest dropping support for Access

Thanks again for your thoughts.
BK

Bob Hairgrove wrote:

> On Sun, 28 Mar 2004 18:56:03 GMT, Ben <BenNOSPAM_at_NOSPAMknieff2.com>
> wrote:
>
>

>>Hello group-
>>I am re-working the structure of a database, and 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.

>>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.
>>
>>For example - I see an address as an entity. The pseudo-DDL would look 
>>like this:
>>	table Address
>>	Street Address 1	alpha,
>>	Street Address 2	alpha,
>>	City			alpha,
>>	State			alpha,
>>	Postal code		alpha,
>>	Country			alpha
>>
>>It seems to me the only unique key would be combination of all of these 
>>properties. 

>>In the past, I would add an integer ID field as the key and 
>>leave it at that. I have read numerous postings suggesting that this is 
>>poor relational design, many hinging on the fact that the generation of 
>>of these IDs tends to be specific to each DBMS vendor. As portability is 
>>a concern, I don't want to make a _big_ mistake. Additionally, I have 
>>run into issues on the application development side when using generated 
>>keys. So, what is the best way to define a primary key for an entity 
>>such as this one?

>>Example 2 - I see a person as an entity. SSN is a good candidate key for 
>>a person, but in my situation, that information will frequently be 
>>unknown. I see no other candidate keys. Yet if it is poor practice to 
>>'manufacture' a key, what approach should be taken?

>>I fear that I have missed something fundamental in my understanding of 
>>relational design, because I know I'm not the only one to come up 
>>against this. Any thoughts or resources are very much appreciated.
>>
>>Thank you,
>>Ben