Re: Normalization, Natural Keys, Surrogate Keys

From: Bernard Peek <>
Date: Tue, 28 May 2002 17:54:52 +0100
Message-ID: <vlO0BLzcZ788Ew$>

In message <>, Costin Cozianu <> writes
>In your example you have perfectly good natural keys, that don't
>change often.
>Also state codes are pretty much all you need for US and Canada
>states. Don't know for India, South Africa and other places.

This is a situation where you don't have control over the data so some form of surrogate looks a good idea. If you allow free-text entry into a field you might find two addresses in Georgia that are 8,000 miles apart.

This is a common problem and a lot of people have got together to fix it. There are ISO standards for country codes. These codes are examples of a surrogate key maintained by a trusted third-party, just like the SSN. Using the ISO codes relieves the developer of the need to dream up new codes and ensures compatibility with other databases.

Bernard Peek

In search of cognoscenti
Received on Tue May 28 2002 - 18:54:52 CEST

Original text of this message