Re: Avoiding surrogate keys

In response to Thom Brown <thombrown(at)gmail(dot)com>:

> I think I know what I plan to do, but want to throw this out there to see if
> there are differing points of view.
>
> I have a mailing list table, and 2 of the columns contain values which have
> to be from a list. These are country and status. 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, 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.
>
> So instead of
>
> name, email, country, status
> 'mr smith', 'emailaddress(at)example(dot)com', 44, 2
> 'mrs jones', 'me(at)emailcompany(dot)com', 21, 1
>
> I'd have
>
> name, email, country, status
> 'mr smith', 'emailaddress(at)example(dot)com', 'China', 'Registered'
> 'mrs jones', 'me(at)emailcompany(dot)com', 'Belgium', 'Unconfirmed'
>
> The values of course would be constrained by foreign key lookup to their
> associated tables.
>
> Are there any serious downsides to this? If so, what would you recommend?

I'd use an ENUM for the status, as that's not liable to change.

The only problem I see with avoiding the surrogate key for the country
is that the table might require more disk space if a lot of the country
names end up being very long.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/