On 08/03/2011 09:24 PM, Darren New wrote:
> Orchid XP v8 wrote:
>> Pro-tip: Don't use real-world things as primary keys. Because you know
>> what? Real-world things have this nasty habit of *changing*. You don't
>> ever want your primary key to change. This is why databases up and
>> down the country refer to Mr Smith not as Mr Smith but as customer
>> #238523.
>
> I disagree. The only time you should use automatically-generated primary
> keys is when there is no attribute of the item in the table that is
> guaranteed not to change.

The recommendation comes not so much from me, but from Tom Kyte. (He's 
an Oracle expert. I'm not entirely sure, but I believe he actually works 
for Oracle Corp.) The assertion being that "stuff that doesn't change" 
is rarer than you think it is.

But sure, especially if you're recording historical data (which is of 
course especially unlikely to change), there's no real need for a 
synthetic key.

> A sales receipt whose primary key is the cash register identifier and
> the time to the second when the receipt was printed is an excellent PK
> for a sales receipt.

Agreed.

> A road
> intersection has a fine PK of the GPS coordinates of the intersection.

Until the GPS coordinates turn out to have been inaccurate. GPS is only 
accurate to a few hundred yards of course, so different people will 
probably get different measurements depending on where they're standing. 
That's probably not a great idea. (Unless you explicitly round the 
coordinates up to some number that makes the size of the actual 
intersection negligable.)

Then again, most intersections probably already *have* a synthetic key. 
I don't know about your country, but around here major roads tend to 
have uniquely numbered markers on the roadside...

> The problem is that people are changing all the time, so it's very hard
> to have a customer record that persists with a PK that makes sense.

Quite.

> The drawback of this is if you get a reference to the wrong value,
> you'll never notice.

Well, perhaps. But if somebody gives you the wrong phone number, you'll 
"never notice" in the same sense...

-- 
http://blog.orphi.me.uk/
http://www.zazzle.com/MathematicalOrchid*

Post a reply to this message