Primary Key Theory Question
Date: Sun, 28 Mar 2004 18:56:03 GMT
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.
Ben Received on Sun Mar 28 2004 - 20:56:03 CEST