Hello, Daniel.

>For good natural key choices (US State abbreviations, DOT Codes), read my response to George Tasker in the Color of Disabled – Gray thread.

I made my point also about US state abbreviations being a totally local thing that is not general and is a design limitation for me. Tell me what are you supossed to do with state abbreviations when you have a foreign customer. Would you have a different way to track customer depending on where they are?

>The following is an example of surrogate keys that can cause problems. In one of our applications, we have a table that has around 200 fields, about 80 of which are codes that are primary keys in lookup tables (these codes carry well understood meaning and none of them has changed in at least 10 years; furthermore, it is very unlikely that any will change in the near and not so near future.

I can't imagine a listing where you print 80 columns with codes without a single description. If it is the case, then you don't need surrogates, but you don't need intelligent keys neither. You are storing just a reference value. If you need a table to store any of this codes toghether with a description and other meaningful data, then I see no reason to ddon't use surrogates.

>In all your posts I have read so far, you say I should use surrogate keys because (1) it removes the need of a cascade update, and (2) consistency. In response to (1): a code value change is considered so unlikely (it would require a lot of effort from many organizations) that we consider the lack of a native cascade update a non-issue. In response to (2): sure we could add surrogate keys and add an additional 80 tables to the join condition when we want to print our main report.

I think you are confusing my arguments with someone elses's. I never mentioned cascading updates. I did mentioned some cases related, but never used the term as strong reason to surrogates. As far as my expirience goes, however, 10 years without change says nothing and using that kind of parameters during the design of a database are very dangerous. The same reasoning could lead to put two or three fields to hold some alternative values in a single record because in XXX years nobody used more than that.

Finally, if your case is SO particular that you really need to join 80 tables for a single report, and there is not a problem in the design, and your users are really able to read such a report, then what's complex is the system, not the methodology.

>IMO, the proponents of pure surrogate keys put too much emphasis on an issue (Cascade Update) that may be a non-issue and throw the consistency buzzword around to put more credibility on their statement. I don’t think the consistency label does not add any additional credibility: consistent can be used with both bad or good and any rule that is consistently followed blindly is bad IMO :-).

I don't think consistency is a buzzword. Indeed, I hear the word very little outside of this debate (I'd like to hear it more often). And I think that at this point you can't think we're following any rule blindly. Any of the people that argued in favor of the always-surrogates strategy had exposed strong arguments and verified them with their experience.

See you.