Re: Avoiding surrogate keys

In response to "Joshua D. Drake" <jd(at)commandprompt(dot)com>:

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