From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Rob Sargent <robjsargent(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Recursive Arrays 101 |
Date: | 2015-10-26 17:14:58 |
Message-ID: | 562E5F92.7010007@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
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.
>
>>> 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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Josip Rodin | 2015-10-26 17:31:30 | Re: Where do I enter commands? |
Previous Message | Lasse Westh-Nielsen | 2015-10-26 16:39:27 | Re: Service not starting on Ubuntu 15.04 |