Re: Recursive Arrays 101

Sorry for the late response. I don't have Internet access at home, so I
only post from the library or a WiFi cafe.

Anyway, where do I begin?

Regarding my "usage patterns," I use spreadsheets (Apple's Numbers program)
to organize data. I then save it as a CSV file and import it into a
database table. It would be very hard to break with that tradition, because
I don't know of any other way to organize my data.

On the other hand, I have a column (Rank) that identifies different
taxonomic levels (kingdom, class, etc.). So I can easily sort a table into
specific taxonomic levels and save one level at a time for a database table.

There is one problem, though. I can easily put all the vertebrate orders
and even families into a table. But genera might be harder, and species
probably won't work; there are simply too many. My spreadsheet program is
almost overwhelmed by fish species alone. The only solution would be if I
could import Mammals.csv, then import Birds.csv, Reptiles.csv, etc. But
that might be kind of tedious, especially if I have to make multiple
updates.

As for "attributes," I'll post my table's schema, with a description, next.

On Mon, Oct 26, 2015 at 10:44 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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(dot)klaver(at)aklaver(dot)com
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
David Blomstrom
Writer & Web Designer (Mac, M$ & Linux)
www.geobop.org