Setting Up A Taxonomy / Evolution Database

[Yahweh]

In another thread, I mentioned I was going to start an Evolution Picture Book project.

Basically, the project entails creating a thorough documentation of animals evolving through their transitional forms, with neat illustrations for every animal.

And, of course, I had intended to put all the information in a database for easy storage, access, and manipulation.

This is not programmatically difficult to do, but thinking out of the logical structure is.

Ideally, I'd like to have a display like this:

Miacis
|
|-Cynodictic
| |
| |-Tomarctus
|   |
|   |-Grey Wolf
|
|-Smilidon
|
|-Dinictus
  |
  |-Weasels
  |
  |-Jaguarundi
  | |
  | |-Wildcats
  | | |
  | | |-House Kitties
  | |
  | |-Tigers
  | |
  | |-Jaguars
  |   |
  |   |-Leopards
  |
  |-Tigers

(The above is my improvisation, it is probably not a very accurate model of descent.)

It looks simple enough, but setting a table is actually very difficult.

On top of that, I'm not sure whether I have two tables, one for Taxonomic Classification and another Descent, or whether Taxonomic Classification and Descent are the same thing, or take a different approach.

Suggestions?

 

[Dymanic]

I imagine you already know about this?:

http://www.tolweb.org/tree/

and this?:

http://ucjeps.berkeley.edu/map2.html

 

[DickK]

Hi Yahweh.

I hope I've understood you, here's what I'd do:

Can't help you with the question about the descent and taxonomy hierarchies being different so I'll address both your options.

Single hierarchy: Single table, a catalogue of animal types (sorry, don't know what you'd call all of these collectively), with an attribute indicating the "parent" of that animal type in your hierarchy. You may add further attributes describing the animal type, of course, "habitat" for instance.

[B][i]Animal Type      Parent[/i][/B]
Grey Wolf        Tomarctus
Weasels          Dininctus
Tiger            Jaguarundi
etc

Multiple hierarchies (including one): Two tables.

1) The animal type catalogue, without the "parent" attribute.
2) A hierarchy relationship table consisting, minimally, of "animal type", "parent" and "hierarchy type". The entire tuple uniquely identifies each row in the table. This will allow you to join an animal catalogue occurrence to its parent animal catalogue occurrence, maintaining the coherence of your hierarchies.

You might wish to add attributes to the hierachy table if you wish to further describe the relationship.

The two table model is fairly ubiquitous and is typically used to represent networks of relationships. If you didn't know, a hierarchy is a special case of a network, so the general case fits elegantly enough.

Sorry if this is not clear, this would be easier with a flip chart and some smelly marker pens. I trust you're happy with the sort of SQL you'll need to interrogate this.

Good luck.

 

[Soapy Sam]

So, Yaweh- what do you plan to do after lunch?

You realise this is a BIG project?

 

[cyborg]

Ideally, I'd like to have a display like this: ... It looks simple enough, but setting a table is actually very difficult.

No point trying to make a table based database act like a heirarchy. What you want to do is simply ensure the tables store all the necessary data for whatever reads the database to infer the necessary information in order to generate a representation in whatever structure it wants.

If you have a table comprising of all the various heirachies of the taxanermy (sp) as one tuple that would be the simplest. You can ensure some consistancy by making the lowest level heirarchy a key and other levels foreign keys by storing the valid names for that heirachy in another table. For example (noting I can't really remember the specification):

Species Relation

Genus Family Kingdom
Homo Sapiens Animal

Kingdom Relation
Animal
Fungus
Plant
Microbes

Anyway, this all rather depends on how you intend to display and access this heirarchy as to what type of relation would work best but clearly the simpler it is the easier it will be for you.