Re: identity columns

John,

My remarks are addressed to the issue of "natural keys" versus "surrogate keys". I have a vague suspicion that the "identity" issue covers more ground than this, but it's useful to explore the surrogate key issue first.

Let's take a typical natural key, like "PRODUCT_ID". Further, let's say that different departments assign product ID's independently of each other, with some protocol in place to keep the same Id from being assigned twice. But let's say that in the course of analysis, we come up with the fact that, in the past, the same Product Id has, in fact, been assigned to more than one product, by mistake.

Now the question becomes, "what do you do when that happens"? One answer is that one of the two products gets its original Product Id pulled, a new product Id is assigned, and all records that used the "wrong" product Id are retroactively updated to refer to the corrected ID. Another answer is that each department continues to use the product id, even though they conflict, and "if you database people want to put them in one big table, I guess that's your problem, isn't it?" This sounds absurd on its face, but I have heard it too many times in the real world to dismiss it as a consideration. In a case like this, I would use surrogate keys.

A second case is where the assigning authority is not accountable to the owners of the database. Every now and then, the Social Security administration has assigned the same SSN more than once. They are not accountable to your company, so if you make SSN a primary key of a table, and you get duplicates, it's you problem. I'll admit it: sometimes I've gone ahead and risked it, and used SSN as a natural primary key.

Ralph Kimball, in his books on data warehousing, discusses the benefits of using surrogate keys to link fact tables and dimension tables. I tend to follow his advice.

Apart from the exceptions given above, I would use natural keys when possible.

--
Regards,
    David Cressey
    www.dcressey.com
"John" <ru_re_re_move_zel_at_ecl_re_re_move_ipse.net> wrote in message
news:JnQY7.359$8j1.340017305_at_news.netcarrier.net...