"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
[quoted text, click to view]

There's also the issue that changing a username will impact on other users
who are playing against them. This is a chess site where people can log on
and make their move at any time during the day. If they open a page and that
page stores a reference to another's username, then they will get problems
if the username is changed. Especially if 2 people swap usernames for some
reason.

Michael

[quoted text, click to view]

I don't think that would be a huge issue. It wouldn't really matter if Roy
and Sylvain swapped their ratings, their entire history would go along with
them.

Michael

[quoted text, click to view]

I'm sure if a third party made any swap that user would not be happy.
[quoted text, click to view]

[quoted text, click to view]

Validation and verification. Consider a VIN on your automobile. No
matter where I go on the entire planet, it identifies that one
particular vehicle. Would you like to let everyone give their
automobile a nickname? Sure, kids do it ("Greased Lightning" if you
are into musicals). Why not let every database create a local key,
unknown to anyone else on Earth?

This fails in the real world; local created keys do not work for
manufacturing or a legal title system, law enforcement, etc. Natural
keys work but they require more effort on the part of the programmers
and DBAs. Cowboy coders hate research and work -- they want to "git
'er done" to quote Larry the Cable Guy.

The performance argument is a few decades out of date. We have much
bigger, faster machinery today --32 and 64 bits versus 16 bit
hardware; nanosecond speeds; massive parallelism; etc. Do you think it
will be worse, the same, better or MUCH better in 5 years?

[quoted text, click to view]

Solid?? You are being local, dialect and lazy. First of all, what you
are talking about are NOT surrogate keys (look up Dr. Codd's articles
-- surrogates are not exposed) butexposed physical locators (generated
by the hardware and software on one machine with one particular
release of one particular product).

If I have to do an artificial key, then I would take the time to
carefully design my key; I have a few thousand words about that in my
books. IT IS HARD WORK. That is why we SQL Gurus make the big bucks.

[quoted text, click to view]

Natural keys will always have to be used - just not necessarily for RI.

You must use natural keys to prevent duplicate data. Sure, there are the
occasional "hall closet" log tables where it doesn't matter, but those
are typically not "real" data - i.e., not directly externally provided.

The fact is, I use both natural and surrogate during implementation -
there is the primary key discovered during design (no arbitrary
surrogate keys at this point), and possibly a row identifier to use for
RI. Not having to cascade updates - which isn't always allowed in DRI,
due to possible cyclic relations - is a huge performance gain.

For implementation, I prefer using identities for surrogates - they're
solid (as long as the column is constrained to be unique), they're
efficient, and they generate quickly (I don't have to roll my own).
I don't make the row ID the PK constraint, however. I put the PK
constraint on the primary key. After all, the row ID isn't real data,
it's just an arbitrary ID - like a bolt holding together a frame. RI can
be done on a unique constraint. Sure, you *can* put the PK on the row ID
and a unique constraint on the logical primary key - but how do you
identify the logical PK if you have multiple unique constraints? By
constraint name? ick.
I also don't use identities on every table, even on those that are
referenced. e.g., Domain definition tables (lookups) are often more
efficient to use later if they are human readable.

I have yet to find a logical data model design application that I like
to use. I identify that "x relates to y 1:n," not that this relation is
"on column z". In design apps, you have to identify the relating
column(s), so you either have to give a surrogate key during logical
design, which seems a knee-jerk reaction; or you have to let it

Good question, I suppose that the same could be asked about religions, wars,
politicians and some others more or less pleasant things that you encounter
in life.

There are three things in database design that I stopped using many years
ago wherever it's possible (ie., when it's me who is taking the decision) :
natural keys, composite keys and Ascii. Now, if I found someone who is
storing foreign caracters in a composite key based itself on natural keys; I
will tell him that's probably a bad idea. However, if he wants to persist,
do you really think that I would care or bother about him or her?

I won't care no more about him (or her) that I would care about someone
willing to give his money to a sect (or a church) in order that his gourou
can change his collection of luxury 2007 cars for a collection of new luxury
2008 cars. Hey, it's his money (and time), not mine.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


[quoted text, click to view]

[quoted text, click to view]

There's been several very good issues raised relating to natural keys that
have gone unanswered here. I have to ask, if the natural key crowd don't
have answers to these questions, why do they still use them?

[quoted text, click to view]

[quoted text, click to view]

Hi Michael,

I'm not sure if I am in the "natural key crowd", nor in the "surrogate
key crowd". I'm one of those silly types that thinks that there is no
one-size-fits-all solution and you have to decide on a case by case
basis.

I don't have time to check the entire thread for unanswered good issues,
so let me just spill some of my thoughts and then you can come back to
me if you feel I have left things unaddressed.

First, I think that developers should let the end users dictate how the
computer works, not the other way around. Since I am convinced that end
users already have some system to distinguish one customer from another,
one order from another, one payment from another, and one product from
another, I prefer to follow their method. Most people call this a
natural key; I prefer the term business key (after all, there is nothing
natural about my SSN, it's nowhere found on my body - but it is the key
that the government "business" uses). Only when there is no adequate key
in use in the business will I work with the end users to help them find
a good key.

But after this first step comes the second: implementation. There are
cases where the business key just doesn't cut it for use in the
database. The most obvious ones are:
* Business key is subject to frequent change
* Business key is very long (e.g. varchar(300)) or composed of many
(three or more) columns
In such cases, I will check if the table is referenced elsewhere. Unless
there are no of very little references, I will introduce a surrogate key
as defined by Dr.Codd, implemented in SQL Server with an IDENTITY
column. I will either make the surrogate key the PRIMARY KEY and define
a UNIQUE constraint for the business key, or vice versa. All references
in the database to the table will be implemented through the surrogate
key, but all references from outside the table will continue to use the
business key.

This way, I still can offer the users the continued use of the
identification scheme they are accustomed to, but I don't get the
performance hit that I would get by implementing references with a
frequently changing, composite, or very long business key.

--
Hugo Kornelis, SQL Server MVP

[quoted text, click to view]

Hi Joe,

If you quote Dr. Codd, quote him in full. Surrogate keys are not exposed
to the database users. He never said that surrogates should not be
exposed to developers or DBAs.

[quoted text, click to view]

An IDENTITY is neither physical, nor a locator. And if you stop using
SELECT * in your view definitions, it's not exposed either.

--
Hugo Kornelis, SQL Server MVP

[quoted text, click to view]

Hi Joe,

So how exactly does this blahblah relate to the issue at hand, which (if
I understand the messages correctly) is about either using the natural
key as the only key of the table, or _adding_ a surrogate key to solve
most of the problems that are caused by changing a natural key?

[quoted text, click to view]

We also have much larger databases. Currently, the amount of data stored
in databases is growing at a faster rate than the processing power of
our computers. Unless that trend reverse, if will be MUCH worse in 5
years. If not sooner.

If you get called in for a consultancy gig and deliver a product that is
blindingly fast on your 100 MB test data but slows down to a crawl on
the company's multi-terabyte DB, do you tell them that the problem will
solve itself as the hardware evolves over the next years?

--
Hugo Kornelis, SQL Server MVP

Well said.

Often the 'real world'... you know, the one where people pay you money
to implement their ideas... dictates how things will look. Often it is
not pretty, often it is not efficient from the purist's sense. But it
works and it deals with the real world in ways they are familiar.

We can and should advise but in the end we have to deliver. The customer
is always right, unfortunately, and not all of us have the time to write
book about it :-)


Geoff Schaller


[quoted text, click to view]