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.kla...@aklaver.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.kla...@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 > -- David Blomstrom Writer & Web Designer (Mac, M$ & Linux) www.geobop.org
