Re: Avoiding surrogate keys

bill-

static information such as country names *should be* de-normalised into non-indexed columns of the driving table as you have already done

if on the other hand the column information country names were changing were dynamic then you would want to keep them in their respective table

as country code 001 will always be US (and the remaining countries and country code will never change)
i would suggest keeping the full name in the driving table (same goes with state/province data btw)

it also saves the database the I/O and CPU from having to do a lookup on another table

my 2 cents
martin-
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

> Date: Wed, 21 Apr 2010 15:07:35 -0400
> From: wmoran@potentialtech.com
> To: thombrown@gmail.com
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Avoiding surrogate keys
>
> In response to Thom Brown <thombrown@gmail.com>:
>
> > 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@example.com', 44, 2
> > 'mrs jones', 'me@emailcompany.com', 21, 1
> >
> > I'd have
> >
> > name, email, country, status
> > 'mr smith', 'emailaddress@example.com', 'China', 'Registered'
> > 'mrs jones', 'me@emailcompany.com', '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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

Hotmail has tools for the New Busy. Search, chat and e-mail from your inbox. Learn more.