Hi, Daniel.

It's a pleasure to disagree with you. 8-)
Seriously, I like the way we can talk even having boldly different points of view.

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

Of course. I know not applications are simmilar to the ones that pay my bills. My concept is with local or very specific things in general. Using your example, I think that I get a contract to write a national census software, giving the complexity it can have, I'll probably try to make it flexible enough to sell it to another country. And of course, maybe in that particular case (inmense amount of data, lots of segmentation, need for multidimensional analysis), maybe I wouldn't use a relational database at all.

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

Again, you sound like a cowboy for me (just a joke). I don't really know if a Super Bowl is a baseball or a football championship. Anyway, I assume your example can apply to any sport, and in that case, I'd use a surrogate without doubt. WHY? Because it is not impossible that I have non-anual championships, extraodinary games (may be exhibitions), and a lot of things that just don't fit in a yearly scheme. Of course I should have the year as a kay for getting information, but a primary? If there is one rule I devoted to myself prior to using surrogates ever, was to NEVER use dates or times as a PK. I can explain my problems with this approach, too.

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

Ok. I got the point. I have used the terms interchangeably in this thread to refer to any key that has a meaning outside the DB, so we're pretty much in the same arena.

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

I think I made my point about that.

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

I'm sorry. I can't really imagine this report. Maybe I could have ended using your solution, but I think most probably would have approached the problem totally different. Can't express an opinion.

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

I suppose you did, obviously. My experience tells me the advantages of surrogates offset that kind of drawbacks. Maybe the point is that I'm accustomed to changing requirements, but I really always preferred to think that things can change, and I never had found myself overcomplicated by doing so.

As I said, supossing that something would not change (no matter how stable some things are) can justify -for example- hardcoding values in a program. We have an internal rule about avoiding this, no matter how "permanent" the value seems to be.

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

The fact is that my experience tells me different. Of course I'm not advising anyone to use surrogates because I think is cooler or more "theoretically" correct, but because I firmly BELIEVE that is absolutely better. And I understand perfectly that your advise could be different.

I just can't advise anyone on something that I don't believe in.

See you.