On Wed, Apr 21, 2010 at 3:01 PM, Thom Brown <thombrown@xxxxxxxxx> wrote: > 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? Natural keys: *) force formal relationships into your key design (this is good) *) Make your database MUCH easier to follow, browse, and understand *) in particular cases allow you to skip joins *) will make your indexes fatter (this is not good) *) can be a pain if your keys are updated frequently *) can be a major pain if your key changes in structure (adds a field, or changes in type) Surrogate keys: *) Give you faster joins, but more of them (this is a win/loss depending on circumstances) *) Tend to encourage lazy/poor designs, since you hide relationships behind a value *) Make the tables more difficult to browse and understand *) Make updates to keys/key structure trivial I personally use natural keys when I can and surrogates when I have to. When I do use a surrogate, I tend to still define the natural key as primary and simply make a alternate 'unique' constraint for the surrogate. merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
