> I definitely agree with you here, Merlin. Mutability is not the issue > at hand. May I ask what strategies you use for determining uniqueness > for people?
Well, that depends on the particular problem at hand. If you had two john smiths in your system, how would you distinguish them? If you assinged an account number in your system and gave it to the person to refer back to you, this ok...this is not a surrogate key per se, but a meaningful alias. However, that technique can't always be applied, take the case of the 'contacts' table for an account. Since you don't give each contact of each accunt a number and you don't print a number representing them on various reports (there would be no reason to), adding a surrogate to the table adds nothing to your database, it's just meaningless infromation with no semantic value. There *must* be a semantic difference between the two John Smiths or you should be storing one record in the database, not two. If you kind determine an easy natural differentiator, invent one: create table contact ( account text, name text, memo text, primary key(account, name, memo) ); The memo field is blank in most cases unlees it's needed. Suppose you were filling contact information in your databse and Taking your second John Smith from an account...your operator says, 'we already have a john smith for your account, can you give us something to identify him?' Put that in the memo field and there you go. Now your operator is taking information which has value pertaining to the scope of the problem domain your application exists in. This is just one example of how to approach the problem Now there is no ambiguiity about which john smith you are dealing with. This may not be a perfect solution for every application but there is basically has to be a method of finding semantic unquenes to your data or you have a hole in your data model. Glossing over that hole with artificial information solves nothing. There are pracitcal reasons to use surrogates but the uniqueness argument generally holds no water. By 'generating' uniqueness you are breaking your data on mathematical terms. Until you truly understand the ramifcations of that statement you can't really understand when the practical cases apply. Many of the arguments for surrugates based on mutability and uniqueness are simply illogical. The performance issue is more complex and leads to the issue of practicality. I wouldn't find any fault with a modeler who benchmarked his app with a surrogate vs. a 5 part key and chose the former as long as he truly understood the downside to doing that (extra joins). Merlin ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
