Howard J. Rogers (hjr_at_dizwell.com) writes:
> You are merely confirming my point. That the decision on whether natural
> or synthetic keys should be used depends upon a proper understanding of
> the business rules to be used. Which is a far cry from whoever it was
> who posted that 'primary keys should NEVER have a business meaning'. It
> doesn't sound like that was you, based on the content of this reply of
> yours... therefore, I think it safe to say we agree.
That is true, that our positions does not seem to on separate ends of
the universe like the people who are making these two kinds of incompatible
statements:
- Primary keys should ALWAYS be natural keys (and verifiable, whatever
that means, according to Celko).
- Primary keys should NEVER have any business meaning.
I agree that neither of these positions are tenable, but my position is a
lot more closer to 2, and if you replace NEVER with NORMALLY. However,
the interpretation of 2 may need some modification.
Basically we can identitfy four kinds of keys:
- Natural keys defined by some other system, like a person identification
number, an ISIN code for a financial instrument, or a currency code.
- Natural keys defined by our own system. A customer opens an account with
a financial institution, and this account gets an account number
which is defined by our system. The account number is exposed, used
by the users, and can be fed to or from external systems.
- Completely synthetic keys, which are not exposed to the user, and
which used internally. Often they go in parallel with some alternate
key, (which may be a semi-unique key, and thus not a real key in the
RDBMS sense.)
- A composite which is composed by keys from other tables, which may of
any of the other sort. This can in fact say be the same as A, even if
all keys are surrogates in the original table.
Keys of type B are not really a problem. Neither are keys of type D
really, although you can see developers who put IDENTITY keys in all
their tables, even those where you have a good composite key. But these
experience often have poor knowledge of database design. And for the
statement that started this thread "no business meaning", and composite
key which consists solely of the C type of keys does not violate this
statement.
So when you use keys of type A? Well, above I had three examples:
- Persons. In some cases the person identifcation number can be used,
but in many cases not. Many systems do B - that is they assign you a
customer number, and may not even care about your person identification
number. (Which you as a customer may appreciate, if you care about your
privacy.) The system I work with do C - that is we don't expose the
customer number, but the user finds a customer through the name or
the person identification number, and the user will have to sort out
the ambiguities. Most often they probably use the account number,
though, and this is a B type of key. (A customer may have many
accounts, and an account may have many onwers.)
- Financial instruments. This area is a complete mess, and anyone who
would try to use a natural key would be in for a disaster. There are
several competing global identification schemes, of which none is
whole-covering. So we use an internal ID of type C, but requires the
user to enter a unique name. (Names can change, and there are over
50 table referring to instruments, some of them biggies.) User can
also use ISIN-code to find an instrument.
- Currency codes. Here it would be a folly to use a key of type C. There
is a commonly used code for currency codes, and even if there are some
people that don't obey them complete (The Brits seems think that there
is something called UKP. There is not.), this is not a big issue. A
country may change its currency, but then usually the value changes
too. For instance Poland went from PLZ to PLN about 10 years ago.
1 PLN = 10000 PLZ, so this affects outstanding values.
And when in doubt - use C.
--
Erland Sommarskog, SQL Server MVP, esquel_at_sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
Received on Sun Aug 01 2004 - 17:07:56 CDT
