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: (view raw or whole 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.
The only problem might be if historical data uses old boundaries (e.g. 
Soviet Union, Yugoslavia).

   Richard Huxton
   Archonet Ltd

In response to


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-2015 The PostgreSQL Global Development Group