On 08/03/2011 10:34 PM, Darren New wrote:
> 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.

Seems reasonable. And I'm guessing that Mr Kyte was talking about huge 
multi-TB data warehouses where updating or removing all references to 
something would be a major PITA.

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

This also seems like a sensible way to go.

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

The point is *totally* to have a PK that never changes, since changing a 
PK is a tricky business which you want to avoid. (Duplicate PKs would of 
course be much, much worse, but that's fairly easy to avoid.)

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

OK, well I'm gonna have to say they must be doing something more precise 
than "stand in the general vicinity of the intersection, take down the 
GPS coordinates, and we'll just use those".

>> GPS is only accurate to a few hundred yards of course,
>
> Uh, no. What are you smoking?

Hey, the satellites are 20,000 km up in the sky. It's astonishing that 
GPS can even figure out which city your in, never mind fixing your 
location to a few hundred yards.

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

So, what, you pick a GPS coordinate at random that's approximately in 
the right area? (So long as its unambiguously not near any *other* 
intersection.)

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

They seem to just be numbered sequentially along the length of the road.

> The point of using GPS coordinates is that they are guaranteed not to
> change.

Sure. Unless the GPS coordinate system moves, which can't possibly 
happen. Oh, wait...

Post a reply to this message