Martin:

> 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?

Some applications don't have "customers" but may be used to track something
specific to a locale. For example, a national census gathers a lot of
information that are specific to the country and hardly, if any, information
about foreign countries.

One very simple of a good use of a natural key is using the year as the PK of
a table that tracks the Super Bowl winners. Note that I would suggest to use
a surrogate key for the team table because some teams do move from time to
time.


>> 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.
>>
>> 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 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
> don't use surrogates.
>
> 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.
>
> 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.

First, a precision. I used the term natural key, not intelligent
key. The distinction is important to me because my understanding of the
terms is as follows:

A natural key is a candidate key carrying a meaning and is independent
of the other keys in the same table.

An inteligent key is a key that depends wholely on one or more other
keys in the same table.

I support the use of natural keys, not of intelligent keys. For example, I support the use of the DOT Code and of Year as natural primary keys but I do
not support the use of composite keys because I believe primary key values should be almost 100% static (changes under very rare circumstances are
acceptable) and having more than one attributes in the intelligent increases
the likehood of a primary key value change.

Second, about complexity. The report does not display description for these
codes because the targeted audience understand the general meaning of these
codes. The lookup tables store additional meaningful information that is not
relevant to this report. We chose one of the existing column as the natural PK
for these lookup tables because it was deterimined that their values were very
unlikely to ever change. We determined that the use surrogate keys would
unduly create additional complexity to this and other reports.

Third, a perceived misconception. In the design of this system, we based our
choice of natural keys on the therory that these values were very unlikely to
change (future), not on the fact that they had not changed recently (past).
The fact that none of them has changed since the original system went into
production 10 years ao tells us that we made a good choice.


>> 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 adds 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.

I don't think you follow this rule blindly but neither do I. I believe that
people who ask questions about surrogate keys deserve to hear both sides of the
issue and I am doing just that. Note that I am not against surrogate keys, I
am just pointing out that the use of a natural key may be a better alternative
if (1) the natural key values are meaningful and (2) they are _very_ unlikely
to change. Readers may disagree with me after hearing my arguments and that's
fine with me because they evaluated the situation.

Daniel