Martijn van Oosterhout wrote:
On Fri, Nov 23, 2007 at 09:33:13AM +0000, Peter Childs wrote:
I tend to agree that primary keys should be single fields if they need to be
referenced but should also be natural if at all possible. ie use car number
plates rather than some serial int.
Car number plates are unique over time? I didn't think so...
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.
Cheers,
Steve
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
