Avoiding surrogate keys

From: Thom Brown <thombrown(at)gmail(dot)com>
To: PGSQL Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Avoiding surrogate keys
Date: 2010-04-21 19:01:12
Message-ID: r2pbddc86151004211201g354091beyb14c797077182c47@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(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?

Thanks

Thom

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bill Moran 2010-04-21 19:07:35 Re: Avoiding surrogate keys
Previous Message John R Pierce 2010-04-21 18:59:49 Re: Int64GetDatum