Re: Rank taxonomy (was Re: Cladism (akin to masochism))

[jpoling@magnus.acs.ohio-state.edu (Jeff Poling)]

>>   Cool.  Now the question is, are the names just put into a list?  I.e. you
>>just have a column of names going from the species all the way back to that
>>first blue-green algae?
>>
>>    Deinonychus
>>    Deinonychosauria
>>    Maniraptora
>>    Coelurosauria
>>    Tetanurae
>>    Theropoda
>>    etc.
>>
>>    is going to be damn difficult to represent in a relational database......
>
>Actually its fairly easy. Ages ago (before I knew anything about
>cladistics), I started to set up a database of dinosaur clasifications.
>
>I started out setting up by tables for class, subclass, order, suborder,
>infraorder, etc. This quickly became unworkable as new levels kept
>appearing, while there were lots of species which skipped levels.
>
>I then redesigned the database to just use one table which was something like:
>
>                +---------------+
>                | Node No       |<--+
>                | Name          |   |
>                | Level         |   |
>                | Parent No     |---+
>                | (other fields |
>                +---------------+
>
>This was using linnean taxonomy so the level field would contain "class",
>"order" or whatever. By the sound of things, this field could be left out in
>a caldistic database.
>
>I'm not sure if the above is very coherent (its very late and I should be in
>bed), so I'll give an example of your dinosaur (throwing in one or two extra
>nodes).
>
>        Node No         Name                    Parent No
>        1               Dinosauria              NULL
>        2               Saurischia              1
>        3               Ornithaschia            1
>        4               Theropoda               2
>        5               Saurapodamorpha         2
>        6               Tetanurae               4
>        7               Coelurosauria           6
>        8               Maniraptora             7
>        9               Deinonychosauria        8
>        10              Deinonychus             9
>        11              Dromaeosaurus           9
>
>Please forgive any spelling (or other) mistakes. As I say, its late.
>

   Looks like I'll adopt this way of doing things, although I'm probably not
going to use node numbers (IF I can get MS-Access to set up a cascading
referential integrity constraint between two fields in the same table).  Thanks.

   The problem now becomes pulling all the clades for a single animal from
top to bottom.  Recursive joins of unknown number can't be done in SQL.  May
have to write a C program to do it (ugh).

** "A warp core breach is imminent?     ** "Those who trade a little freedom **
** This calls for the handyman's secret ** for a little security will soon   **
** weapon, duct tape." --- Red Green,   ** find that they have none of       **
** Chief Engineer, U.S.S. Voyager       ** either." --- Jeff Poling          **