Re: Primary Key

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 11/26/07 12:11, Steve Crawford wrote:
[snip]
>
> 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).

We use this as a *non*-unique index:
PLATE_NUMBER    CHAR(10)
PLATE_STATE    CHAR(2)
PLATE_COUNTRY    CHAR(4)

The country field could be dropped off and Canada/USA differentiated
by the state/province code, but with NAFTA it's possible that
Mexican plates will turn up "soon", and there's always the off
chance that a European car will show up.

(We used to have PLATE_STATE first, but then discovered how many
northeasterners don't know what state their vehicle is registered in.)

- --
Ron Johnson, Jr.
Jefferson LA  USA

%SYSTEM-F-FISH, my hovercraft is full of eels
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHSzxlS9HxQb37XmcRAh0QAKCLp5aNkoPPs8P5oXQCJ0HI28MNuACeKtFH
eECn8XRwrjOqonUuDr8DDH8=
=cYiG
-----END PGP SIGNATURE-----