Skip site navigation (1) Skip section navigation (2)

Re: Normalized Tables & SELECT [was: Find "smallest common year"]

From: Richard Huxton <dev(at)archonet(dot)com>
To: Alban Hertroys <a(dot)hertroys(at)magproductions(dot)nl>
Cc: Stefan Schwarzer <stefan(dot)schwarzer(at)grid(dot)unep(dot)ch>, pgsql-general(at)postgresql(dot)org, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Subject: Re: Normalized Tables & SELECT [was: Find "smallest common year"]
Date: 2007-09-28 09:21:20
Message-ID: 46FCC790.7050109@archonet.com (view raw or flat)
Thread:
Lists: pgsql-general
Alban Hertroys wrote:
> If you _do_ need this table (because you want to constrain your
> statistical data to only contain a specific set of years, or because you
> need a quick list of available years to select from): Make the year
> primary key and drop the artificial index.
> 
> Years are perfectly fine data to constrain on, and it saves you the
> joins with that table (the foreign key constraint constrains your data
> sufficiently).
> 
> You may want to constrain your years further by adding a check
> constraint, fe. CHECK (year > 1900).
> 
> The same argument goes for the country names, but I generally don't like
> to have text data for primary key.

Ah, but there is a standardised list of country-codes ideal for the DBA 
since the code is (usually) easily understandable.
   http://en.wikipedia.org/wiki/ISO_3166
The only problem might be if historical data uses old boundaries (e.g. 
Soviet Union, Yugoslavia).

-- 
   Richard Huxton
   Archonet Ltd

In response to

Responses

pgsql-general by date

Next:From: Nis JørgensenDate: 2007-09-28 09:24:17
Subject: Re: Why the ERROR: duplicate key violates unique constraint "master_pkey" is raised? - Is this a Bug?
Previous:From: Nis JørgensenDate: 2007-09-28 09:17:17
Subject: Re: Normalized Tables & SELECT [was: Find "smallest common year"]

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group