Re: Recursive Arrays 101

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 <GavinFlower(at)archidevsys(dot)co(dot)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&#8217;s jay
>> Plural - Steller&#8217;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