Re: A second qs on multivalued dependencies.

"Kostas" <noemail_at_noemail.net> wrote in message news:10mvv9d6jn83be1_at_corp.supernews.com...
> Yes, u are suggesting again a natural (semantic) key vs. the surrogate
like
> in the previous question. So this is the tendency in this newsgroup? Use
> natural keys?

I have what's probably the minority view in this forum about natural vs. surrogate keys. I think a lot of so called "natural keys" are really surrogate, when viewed from a distance.

In the first place, there are no natural keys to identify people. We all know why names won't do.
But those people who think that SSN is a "natural" key forget that the Social Security Administration is not a force of nature. Just because the SSN is assigned by some clerk at the SSA rather than some automaton inside the DBMS doesn't make it "natural".

Same for employee_id. A lot of people think this is a surrogate key if IT assigns it, but it's a natural key if HR assigns it. Why?

Having said that, I prefer a natural key if three conditions can be met:

1. I can trust the assigning authority, and I some form of contract, or at least a "meeting of the minds" that says that the assignors recognize their responsibility not to break my system.
2. I don't need to use a surrogate key to make a data warehouse survive the reorganization of the enterprise it supports.
3. I don't need a surrogate key to track the history of the attributes of the natural key.

If a surrogate key buys me something with regard to the above, I'll use it. If not, I'll use the natural key. Received on Fri Oct 15 2004 - 13:25:21 CDT