Creating Taxonomy Table in MySQL
I worked with similar data, and I made it in 2 parts. In PostgreSQL syntax.
First is taxonomy structure (Family, Genus, Species, ...):
CREATE TABLE taxonomic_units ( id serial PRIMARY KEY, name varchar(20) NOT NULL, parent_id integer REFERENCES taxonomic_units(id));1 | Life | NULL2 | Domain | 1...7 | Family | 68 | Genus | 79 | Species | 8
Second is description and storing of botanical data:
CREATE TABLE taxons ( id serial PRIMARY KEY, suptaxon_id integer REFERENCES taxons(id), taxonomic_unit_id integer NOT NULL REFERENCES taxonomic_units(id), name varchar(50) NOT NULL, authority varchar(50));100 | NULL | 8 | Ocimum | L.101 | 100 | 9 | basilicum | L.102 | 100 | 9 | gratissim | L.
I'm not sure I really buy into that article. Graph structures would be needed when the categories itself are mutable. Such as, all the sudden taxonomists decided to add three new levels between genus and species, and so on.
From the article:
... the management of hierarchical data is not what a relational database is intended for.
Actually, its exactly what it is intended for:
The hierarchical data model lost traction as Codd's relational model became the de facto standard used by virtually all mainstream database management systems.
I would first write a view that joined all of your tables so that you would have these as your columns:
Life Domain Kingdom Phylum Class Order Family Genus Species
Now you can query that view any way you like and not have to worry about any joins. Easy
You can download complete taxonomy data from http://itis.gov and the data is updated more or less monthly. The data they provide includes a Materialized Path -- every species in the database has a string of all the levels above it, like a breadcrumbs string or a filesystem path.
I used this data to design a demo in my presentation Models for Hierarchical Data. I converted the materialized path data into Closure Table.