In response to "Joshua D. Drake" <jd@xxxxxxxxxxxxxxxxx>: > On Wed, 2010-04-21 at 15:07 -0400, Bill Moran wrote: > > 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. > > > > Yeah but that isn't generally a problem now and the gain he gets from > the lack of join performance is more than worth it. Agreed. I guess I didn't explain properly: the disk space _may_ be an issue if you have LOTS of REALLY LONG names. Like, if the shortest name in your country table is 'Federated States of Micronesia (Esquire)', then the disk space used by the table and index might become an issue. Also, if this is an embedded application where disk is at a premium ... As with many things, "big" and "small" are relative, ambiguous and copy-written by someone who thinks they can charge me every time I use them. One thing that a lot of people seem to get confused about is that they subconsciously think that ints or bigints take up less space when the numbers are small. I.e.: I want to use an int for my state identifier instead of the 2-digit code, because it will use less space -- wrong, an int is 4 bytes, but a 2 byte char column is 1/2 that ... even if the number never gets higher than 50. Personally, I think the only good reason to use a surrogate key is when there's a high likelihood that your primary text identifier might change. Although ON UPDATE CASCADE can even handle that, it just might take a while. -- 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
