In response to Thom Brown <thombrown@xxxxxxxxx>: > 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? 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/ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
