Avoiding surrogate keys - Mailing list pgsql-general

From Thom Brown
Subject Avoiding surrogate keys
Date
Msg-id r2pbddc86151004211201g354091beyb14c797077182c47@mail.gmail.com
Whole thread Raw
Responses Re: Avoiding surrogate keys  (Bill Moran <wmoran@potentialtech.com>)
Re: Avoiding surrogate keys  (Rich Shepard <rshepard@appl-ecosys.com>)
Re: Avoiding surrogate keys  (Merlin Moncure <mmoncure@gmail.com>)
Re: Avoiding surrogate keys  (Philippe Lang <philippe.lang@attiksystem.ch>)
Re: Avoiding surrogate keys  (John R Pierce <pierce@hogranch.com>)
List pgsql-general
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@example.com', 44, 2
'mrs jones', 'me@emailcompany.com', 21, 1

I'd have

name, email, country, status
'mr smith', 'emailaddress@example.com', 'China', 'Registered'
'mrs jones', 'me@emailcompany.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?

Thanks

Thom

pgsql-general by date:

Previous
From: John R Pierce
Date:
Subject: Re: Int64GetDatum
Next
From: Bill Moran
Date:
Subject: Re: Avoiding surrogate keys