Primary Key Theory Question

From: Ben <>
Date: Sun, 28 Mar 2004 18:56:03 GMT
Message-ID: <7dF9c.3707$>

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 Received on Sun Mar 28 2004 - 20:56:03 CEST

Original text of this message