Orchid XP v8 wrote:
> The assertion being that "stuff that doesn't change" 
> is rarer than you think it is.

I'll grant that it's rarer than most people think it is. The trick is to 
consider whether if the PK changes, you're willing to treat the object as a 
new entity, or whether it's rare enough that you're willing to expunge the 
old ID entirely.

Is it the same telephone pole if I dig it up and plant it somewhere else? 
Sure, maybe. But am I ever going to need to know that if my job involves 
routing cables?  Probably not.  Am I going to need to know that if my job 
involves replacing them when they wear out?  Most definitely.

How often do you replace the little tags on the side of the pole that give 
the pole number? Not very often. Is there any reason if that ID tag gets 
replaced that you can't just update in the database everyplace that appears? 
Probably not.

> 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.

I contend that most primary data in the database should be historical.

> Until the GPS coordinates turn out to have been inaccurate.

Then you fix it. There never *was* an intersection with those GPS 
coordinates. So you fix it. The point is to avoid collisions, not to 
necessarily have a PK that never changes.

Given that the BIGGEST TRANSACTIONAL DATABASE IN THE WORLD (*) identifies 
street intersections with GPS coordinates, I'm gonna have to go with GPS 
coordinates on this one. ;-)

> GPS is only 
> accurate to a few hundred yards of course,

Uh, no. What are you smoking?

> so different people will 
> probably get different measurements depending on where they're standing. 

Sure. But you're not asking someone to identify a real intersection based on 
their GPS coordinates. You're using it as a primary key.

None of your customers identifies themself with your internal ID number, either.

> 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...

Assuming those don't change, they're probably based on geometry also. The 
point of using GPS coordinates is that they are guaranteed not to change. If 
you enter them in the database wrong, then you have bad data, but that's not 
because the intersection moved.

>> 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...

Except I'm not using a phone number as a primary key. :-)



(*) The 300 terabyte TURKS database. The size is probably bigger 15 years 
later, too.

-- 
Darren New, San Diego CA, USA (PST)
  "How did he die?"   "He got shot in the hand."
     "That was fatal?"
          "He was holding a live grenade at the time."

Post a reply to this message