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@xxxxxxxxxxx', 44, 2
'mrs jones', 'me@xxxxxxxxxxxxxxxx', 21, 1
I'd have
name, email, country, status
'mr smith', 'emailaddress@xxxxxxxxxxx', 'China', 'Registered'
'mrs jones', 'me@xxxxxxxxxxxxxxxx', '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?
Thanks
Thom
