Re: Data Types

From: "Jean-Yves F(dot) Barbier" <12ukwn(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Data Types
Date: 2010-11-05 16:52:43
Message-ID: 20101105175243.2f2fcb4e@anubis.defcon1
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

On Fri, 5 Nov 2010 15:17:53 +0000, ALT SHN <i(dot)geografica(at)alt-shn(dot)org> wrote:

> I have a column of a table of paleontological sites named "Paleosites"
> wich has a column named "species". A paleosite can contain remais of one
> or more animals. So in that column sometimes I have to put this:
> dinossauria crocodilia plantae. This indicates that from a given
> paleosite were retrieved the remais of 3 different species.
>
> Now my question is: wich data type should the column "Species" be? Text?
> Varchar?

None, you'd better use 3 tables:
T1: the same without the "species" column,
T2: describes join(s) between T1 & T3 (by their primary keys)
T3: contains all possible species

same example as yours:
T1 id (PRIMARY KEY) = 45

T3 id (PK) of dinotopia = 148
T3 id (PK) of crocodilia = 4
T3 id (PK) of plantae = 973

You just have to create 3 rows into T2:
INSERT INTO T2 VALUES(45, 148);
INSERT INTO T2 VALUES(45, 4);
INSERT INTO T2 VALUES(45, 973);

this way, you won't have to rewrite a specie again and again, and you can
easily add/remove a specie from the site (you can also add another table
for subspecies and join species-subspecies the same way with an
intermediate table, ie: bags, boots, hats, vests for crocodilia)

Read at least:
http://www.tekstenuitleg.net/en/articles/software/database-design-tutorial/intro.html

--
Any girl can be glamorous; all you have to do is stand still and look
stupid. -- Hedy Lamarr

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Wm.A.Stafford 2010-11-05 20:06:07 COPY command and serial columns
Previous Message Andreas Kretschmer 2010-11-05 16:47:40 Re: Data Types