Re: Database design, Keys and some other things

JOG wrote:
> > Marshall Spight wrote:
> > >
> > > Perhaps we have different definitions of surrogate keys. The VIN
> > > may be something that's not a surrogate key in database A, but
> > > it's certainly a surrogate key in *some* database, and hence I
> > > don't see any particular difference between it and any other
> > > made-up identifiers. SOMEone, some database, made up the VIN
> > > out of thin air; we could well say they "implemented [it] to get
> > > [their] system working." So I still don't see the distinction
> > > you're drawing here.
> >
> > There is an important difference. Unless we are talking about
> > that specific "*some*" database, the VIN is /not/ a surrogate key in
> > the database at hand.
>
> I'd answer along the same lines as mAmsterdam. The difference is that a
> VIN is controlled externally to the database (owned by the real world
> as it were) and a pure surrogate key is controlled internally within
> the RDBMS (owned by the database). The distinction guarantees that the
> latter is immutable and unique, which is not the case with the former.

I don't think I agree with the last sentence. In fact, I don't see how the guarantees are any different. Some piece of machinery in the DMV (a dbms, I'm sure) allocates VINs which are then stamped on cars. If I have a customer database table with a lousy autoincrement key (not to make Joe C cringe, but just to keep the argument concrete and simple) then it is every bit as immutable and unique as the VIN in the DMV database. And once I print out statements or a customer list or whatever, that id exists in the real world as well.

> And of course, yes, as soon as these lines blur and the pure surrogate
> key (and by pure i mean solely db owned), starts to be used outside of
> the db, the real world has taken over ownership of it, and it loses all
> its guarantees.

I can't make sense of this sentence. Through what mechanism are the guarantees lost? In what sense are you using the word "guarantee?" Generally when we talk about database guarantees, we are talking about dbms-enforced constraints; that doesn't appear to be what you mean.

Marshall Received on Fri Sep 30 2005 - 16:52:09 CEST