re:Soliciting Architecture Opinions

Guest ,

You have provided an example of a table with 2 candidate keys .

The meaningful key is based on attributes which are mutable , ie may change value . Furthermore it might be discovered at a later date that the key is reducable eg that ZIP code can be removed or that additional attributes like country need to be added .

The aim of complimenting meaningful unique keys with surrogate keys is generally to isolate the system from the impact of the above two situations .

So , getting back to how you should implement this in your logical datamodel .

1. Carry out business analysis to identify all meaningful candidate keys for the entity being represented . If you look hard enough you will find at least one .

CK1 (Name, Address, City, State, Zip)

2) Determine whether any of the meaningful CK's are suitable for referencing

If not , then add a surrogate candidate key

CK2 (CustomerNumber)

3) Decide whether one ckey should be assigned primacy

4) Document ALL candidate keys in the database so the DBMS can enforce uniqueness .

... PRIMARY KEY (customernumber)
....UNIQUE KEY UK1 (Name , Address , City , State , Zip)

5) Ask yourself whether it makes sense to have 2 rows in a table which differ only in the value of their "unique id no" and then ask yourself whether keys should be declared to disallow such a situation .

This situation is almost always indicative of innadequate analysis and/or a cook-book design methodology .

If you are forced to create such tables then protect against duplicates by declaring a key , if neccessary comprising every column in the table except the unique id no .

If you are interested in learning your subject so you will be in a possition to judge whether what people are saying is correct , then you will need to invest time reading the right books . I recommend "Practical Issues in Database Management" by Fabian Pascal and "An Introduction to Database Systems" by C. J. Date . Received on Fri Nov 04 2005 - 02:10:01 CST