Skip site navigation (1)
Skip section navigation (2)
Peripheral Links
Re: 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
Why don't you use a separate table "species" with an associative table in between "species" and "Paleosites" ? Is there any reason preventing it I am missing? Best, Oliveiros ----- Original Message ----- From: ALT SHN To: pgsql-novice(at)postgresql(dot)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(dot)geografica(at)alt-shn(dot)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(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
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, Thanks a lot for all your comments. I'll try adress each suggestion. *Jean-Yves F. Barbie*: Thanks for the link with the tutorial it's very enlightning. I had some notions of RDB theory but this destroyed some erroneus conceptions I had; *Jean-Yves F. Barbi & **Oliveiros d'Azevedo Cristina* 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 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... 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 relation1:m. * * 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? *Andreas:* Yes the "paleosites" table has point geometry taken from a shapefile build in real-time GPS field survey. Thank you so much people! (rookiness can be an awfull thing!) 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 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(dot)geografica(at)alt-shn(dot)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ıʇǝǝɹ⅁