Search Postgresql Archives

Avoiding surrogate keys

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux