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