Re: Primary Key - Mailing list pgsql-general

On Nov 26, 2007 1:11 PM, Steve Crawford <scrawford@pinpointresearch.com> wrote:
> It's worse than that.
>
> If we presume that the plate is a key to a vehicle, then we immediately
> run into problems as a vehicle can, over time, have several plates
> (lost, stolen, changed to vanity...) and a plate can belong,
> sequentially, to several vehicles (especially when vanity plates are
> transferred to new cars).
>
> And when you have your char(6) plate-number column, they run out of
> numbers and switch to 7-characters requiring changes to all tables that
> used the plate as a key. Or you realize that ABC123 could be
> ABC123-California, ABC123-Nevada or ABC123-New York (I'm assuming that
> AAA999 is a valid format in those states).
>
> Although I haven't seen it much, recently, semi-trucks used to regularly
> have with numerous plates - one for each state in which they operated.
> And some states such as Texas allow you to have the same amateur-radio
> plate number on multiple vehicles.
>
> I won't argue that there are no reasonable natural keys. But I have sure
> seen plenty of cases where what appeared to be a natural key was
> discovered, generally at a very inopportune time in the development
> process, to be not-so-natural after all.

if you miss the key and blow it, you fix it.  yes, there are tons of
examples of this particular number not exactly lining up with
something, like a person, vehicle, etc. of course this all means that
the number in question is simply not enough information by itself, and
so is either a partial definition or defines something else.

i will concede that changing a key across 10 tables is easier than
redefining a constraint on one table.  this is why the compromise
mentioned way upthread by josh drake (namely, to define the natural
but use surrogate for joining) is good in certain cases like this,
especially when you have a complex key that is used in many tables.

the problem is that, because surrogates allow skipping the problem
without defining a proper key at all, the vague data relationships you
mention never get properly defined in the database and end up being
caught in code or by the user because the id is trusted to express the
relationship when in fact it doesn't.  this causes much worse problems
than redefining keys by the way, and helps create the messy databases
that those of us who know how to do things both ways complain about.

in other words, if you create tables by defining the id p-key,
throwing a bunch of fields on it that approximately describe the item,
plus maybe some indexes for performance, you have already loaded the
gun to shoot yourself in the foot.  many of the safeguards the
database can provide in keeping your data organized have been
removed...

merlin