Обсуждение: Data Types

Hello List,

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?

Thank you in advance,

Best Regards,

André Mano

--
---------------------------------------------------------------
Associação Leonel Trindade
SOCIEDADE DE HISTÓRIA NATURAL

Apartado 25 2564-909 Torres Vedras Portugal
Sede e Biblioteca: rua Cavaleiros da Espora Dourada, 27A 2560 Torres Vedras

Laboratório de Paleontologia e Paleoecologia: Polígono Industrial do Alto do Ameal 2565-641 Ramalhal
http://alt-shn.blogspot.com
www.alt-shn.org

----- Original Message -----

From: ALT SHN

To: pgsql-novice@postgresql.org

Sent: Friday, November 05, 2010 3:17 PM

Subject: [NOVICE] Data Types

Hello List,

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?

Thank you in advance,

Best Regards,

André Mano

--
---------------------------------------------------------------
Associação Leonel Trindade
SOCIEDADE DE HISTÓRIA NATURAL

Apartado 25 2564-909 Torres Vedras Portugal
Sede e Biblioteca: rua Cavaleiros da Espora Dourada, 27A 2560 Torres Vedras

Laboratório de Paleontologia e Paleoecologia: Polígono Industrial do Alto do Ameal 2565-641 Ramalhal
http://alt-shn.blogspot.com
www.alt-shn.org

ALT SHN <i.geografica@alt-shn.org> wrote:

> Hello List,
>
> 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?

If i where you, maybe a text[]:

test=# create table Paleosites (species text[]);
CREATE TABLE
test=*# insert into Paleosites values (array['dinossauria','crocodilia','plantae']);
INSERT 0 1
test=*# select * from Paleosites;
             species
----------------------------------
 {dinossauria,crocodilia,plantae}
(1 Zeile)


Other solution: an extra table with a 1:m relationship.



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

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

Am 05.11.2010 16:17, schrieb ALT SHN:
> 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.
Do it with 3 tables and use numerical representation of your keywords as
Jean-Yves described.
The technique is called foreign key and normalisation.
This might sound complicated at first but it is much better than writing
everything in a textfield.


As soon as you have a certain amount of data in your database, you will
have to cope with "dynosauria" and "krocodillia". Typing errors will happen.

Please find some time to get a better idea what a relational database is
all about.
Really this will help you a lot.
And you should be prepared to do the whole work again when you learnd
some basics.   ;)

BTW you might be interested to know that PostgreSQL has datatypes and an
addon for geolocations.
You will want to store GPS coordinates for the sites.
I'm sure some others here have allready used this feature. I just saw it
somewhere mentioned.

Hello All,

ALT SHN wrote:
> The reason why I don't want to create separate tables to deal with this
> issue is because this is a provisional information. I'll explain. The

BRAAAAAPPP!

Red herring.

> table "paleosites" is in fact a Shapefile that was imported via PostGIS.
> The column "species" it's only the first impression gathered on field
> work. The real species might take years or even decades to fully
> determine. For example we know that from paleosite "x" we have remains
> of dinossauria but what group? what species? Only laboratory work will
> be able do determine this...

This has nothing to do with normalizing your table to eliminate the array column.

> The column "species" is, therefore, intended to give a first hint. The
> real species- that are fully studied are on a table called "specimens"
> wich is linked to table "species" in a relation 1:m.

So you reassign the species when you get the new data.  Simple.  You're
complicating things unnecessarily.

> Do you thing that even though this issues I'm still modeling the wrong
> way? If I stick to the original idea (enumerating all the species in
> column "species" with datatype text) maybe I could eliminate the spaces
> (DinossariaCrocodilia) or that's just irrelevant?

Yes, you are modeling it the wrong way.  Read up on relational database
normalization.

The notion that the species assignment might change has no relevance to the
flaw in your design.  You still face that issue with a bad design, but you add
the additional problems of the bad design.  Normalize your database and you'll
still have the "problem" that species assignment might change, but it will be
much, much easier to handle correctly.

--
Lew

On 2010-11-05, ALT SHN <i.geografica@alt-shn.org> wrote:
> --20cf30549a6f4e574504944fc733
> Content-Type: text/plain; charset=ISO-8859-1
> Content-Transfer-Encoding: quoted-printable
>
> Hello List,
>
> 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?

For 3NF it should be in a separate table.

Text and varchar are the same just varchar takes longer to type on the
keyboard.

text[]  (array of text) is another option (but both above comments
still apply).

--
ɹǝpun uʍop ɯoɹɟ sƃuıʇǝǝɹ⅁