What does "top post" mean? And what do you mean by "embedded spaces"? Are
you referring to the underscores in the TABLE name?
On Mon, Oct 26, 2015 at 2:12 PM, Gavin Flower <gavinflo...@archidevsys.co.nz
> wrote:
> Hi David,
>
> Please don't top post!
>
>
> On 27/10/15 09:42, David Blomstrom wrote:
>
>> I've created my first table in postgreSQL. I'd like to ask 1) if you see
>> any errors, 2) do you have any suggestions for improving it, and 3) can you
>> give me the code I need to paste into the shell (or whatever you call the
>> command-line tool) to recreate it?
>>
>> This is what the table's schema looks like in MySQL...
>>
>> N - int(6) [Primary Key]
>> Taxon - varchar(50) [Unique Key]
>> Parent - varchar(50) [Index Key]
>> ParentID - tinyint(1) [Index Key]
>> Slug - varchar(50) [Index Key]
>> NameCommon - varchar(50)
>> Plural - varchar(50)
>> Extinct - tinyint(1)
>> Rank - tinyint(2)
>> Key - tinyint(1)
>>
>> The table type is MyIsam, collation is latin1_general_ci
>>
>> Slug, NameCommon and Plural are NULL.
>>
>> All of my tables have a default first column named N or ID, which is
>> simply a numerical key that begins with 1. It's always designated the
>> primary key.
>>
>> All the other columns in this table can be divided into two categories,
>> text (varchar) and numerical (tinyint).
>>
>> The values in the columns Taxon and Slug serve as URL's, so they can have
>> no spaces, apostrophes, accents, etc. (Taxon handles scientific names, Slug
>> common names, if any.) So a row focusing on the Steller's jay would have
>> values like these:
>>
>> NameCommmon - Steller’s jay
>> Plural - Steller’s jays
>> Taxon - Cyanocitta-stelleri
>> Slug - stellers-jay
>> Parent - Cyanocitta
>>
>> The column ParentID - which I want to use for hierarchical relationships
>> - has values ranging from 1 for Mammalia (the first row) to 5 for the
>> species level. The column Extinct has the value 1 (not extinct) or 2, 3 or
>> 4 for various categories of extinct taxons.
>>
>> The column Rank has the value 25 for the first row (class Mammalia), 35
>> for each order (e.g. Carnivora), 45 for each family, 55 for each genus and
>> 65 for each species. The value for Key is 1 (for every row), designating it
>> a tetrapod. The bird, reptile and amphibian tables have the same key value,
>> while fish, invertebrates and plants have their own unique keys.
>>
>> I have Unique keys on N and Taxon, Index keys (not unique) on Parent,
>> ParentID and Slug.
>>
>> My PostgreSQL table is in a database named GeoZoo. When I go into
>> pgAdmin3 > SQLPane, it looks like this:
>>
>> CREATE TABLE public.gz_life_mammals
>> (
>> "N" integer NOT NULL,
>> "Taxon" character varying(50) NOT NULL,
>> "Parent" character varying(50) NOT NULL,
>> "ParentID" smallint NOT NULL,
>> "Slug" character varying(50),
>> "NameCommon" character varying(50),
>> "Plural" character varying(50),
>> "Extinct" smallint NOT NULL,
>> "Rank" smallint NOT NULL,
>> "Key" smallint NOT NULL,
>> CONSTRAINT "Primary Key" PRIMARY KEY ("N"),
>> CONSTRAINT "Unique Key" UNIQUE ("Taxon")
>> [I haven't added any non-unique keys yet.]
>> )
>> WITH (
>> OIDS=FALSE
>> );
>> ALTER TABLE public.gz_life_mammals
>> OWNER TO postgres;
>>
>> I should also mention that Taxon is the column I use to UNION or JOIN
>> this table with other tables.
>>
>> P.S. If I decide to organize things by taxonomic levels (e.g. kingdom,
>> class, etc.), then it should be easy to rename the table, delete a few
>> columns, and refill it with data associated with a particular class.
>>
> [...]
>
> Would suggest using lower case column names without embedded spaces, if
> possible!
>
> NEVER assign tables to the postgres user, application tables should be
> owned by a user!
>
> Note that PRIMARY KEY gives you both NON NULL & uniqueness. So you don't
> need a separate PRIMARY KEY constraint!
>
> 'id' would be better than 'N' for the primary key name. ==> 'id int
> PRIMARY KEY'
>
> Using 'text' rather than 'character varying(50)' would probably be better.
>
> Since you are making a single column unique, suggest 'taxon text UNIQUE
> NOT NULL'
>
> You don't need to specify 'OIDS=FALSE', as that is now the defualt.
>
>
>
> Cheers,
> Gavin
>
>
>
--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org
