Re: Recursive Arrays 101 - Mailing list pgsql-general

On 10/26/2015 10:33 AM, Rob Sargent wrote:

On 10/26/2015 11:14 AM, Adrian Klaver wrote:

On 10/26/2015 08:32 AM, Rob Sargent wrote:

On 10/26/2015 09:22 AM, Adrian Klaver wrote:

On 10/26/2015 08:12 AM, Rob Sargent wrote:

On 10/26/2015 08:43 AM, Jim Nasby wrote:

On 10/25/15 8:10 PM, David Blomstrom wrote:

@ Adrian Klaver: Oh, so you're suggesting I make separate tables for
kingdoms, classes and on down to species. I'll research foreign
keys and
see what I can come up with. I hope I can make separate tables for
mammal species, bird species, fish species, etc. There are just so
many
species - especially fish - the spreadsheets I use to organize them
are
just about maxed out as it is.

The suggestion is simply to have 7 tables:

CREATE TABLE kingdom(
  kingdom_id serial PRIMARY KEY
  , kingdom_name text NOT NULL
  , ...
);
CREATE TABLE phylum(
  phylum_id serial PRIMARY KEY
  , kingdom_id int NOT NULL REFERENCES kingdom
  , ...
);
CREATE TABLE class(
...
);

and so-on.

Seems to me that if life boils down to four attributes one would
have a
single table with those four attributes on the particular life form.

Out of curiosity what are those four attributes? It would have made
memorizing all those organisms a lot easier when I was in school:)

kingdom phylum class genus as attributes in species table.  Talk about
your "natural key".  The hibernate boys would love it :)

Well in this classification system it would need to be:

kingdom phylum class order family genus

Sorry, wasn't tracking carefully: 6 attributes

What makes it complicated is that these are just the slots. How
organisms are slotted depends on attributes and there are a lot of
them. This means there is a constant rearrangement in the slotting.

But at the end of the day, is it not the intent to have those six filled
per species. Is your point that maintenance would be problematic?
Agreed.  Certainly not just a single pointer redirect in a recursive
structure.  All depends on OPs usage patterns.  I personally love 'with
recursion' but it's more complicated than for example
     select count(*) from species where class = '<some class name>'
if, and only if, all 6 attributes are always there.  Which highlights
your caveat "In this classification system".

This is the current system. If you want to be historically complete then you have to take into account the ways things where classified before. Granted this is running in the crawl, walk , run sequence but it cannot be entirely ignored. Then there  are the more detailed versions of the above:

http://www.itis.gov/servlet/SingleRpt/SingleRpt?search_topic=TSN&search_value=584927

It comes done to what view of taxonomy you want to support.

Now, the four attributes could be ids into definitional tables but I
suspect the querying will be done string/name so why complicate the
lookups: make the names a foreign key in the defs if necessary.

Personally I think the recursive structure is the way to go.

Jtbc, I'm not advocating this structure but it may suit the OP's usage
patterns.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general