Re: Avoiding surrogate keys

On Wed, 21 Apr 2010, Thom Brown wrote:

> I have a mailing list table, and 2 of the columns contain values which
> have to be from a list.

Thom,

   From 2 lists?

> These are country and status.

   And each is from a separate list, correct?

> There are 237 possible countries and 3 possible statuses.

> Now I know some people would assign a sequence ID (surrogate key) to the
> country and status values,

   Why? These two fields do not define a unique row, do they? If not, then
neither is a candidate key and should be treated as a regular attribute.

> ... and have them looked up in separate tables to get the textual value,
> but I think I'll still have those tables, just without an ID column, so 1
> column for both the countries and statuses tables.  This means storing the
> proper value in the main table.

   You could have a table with two columns: abbreviation and name. Then you
could copy that table from the Internet to get the data.

> So instead of
>
> name, email, country, status
> 'mr smith', 'emailaddress@example.com', 44, 2
> 'mrs jones', 'me@emailcompany.com', 21, 1

   Not only is more work, but it's confusing and unnecessary.

> I'd have
>
> name, email, country, status
> 'mr smith', 'emailaddress@example.com', 'China', 'Registered'
> 'mrs jones', 'me@emailcompany.com', 'Belgium', 'Unconfirmed'

   Sure.

> Are there any serious downsides to this?  If so, what would you recommend?

   Nope. Not even flippant downsides.

Rich