Re: Data Types

On Fri, 5 Nov 2010 15:17:53 +0000, ALT SHN <i.geografica@alt-shn.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