Creating Taxonomy Table in MySQL

Question

I am creating a botanical database where the plants will be organized by their taxonomy:

Life Domain Kingdom Phylum Class Order Family Genus Species

I was considering using the example put forth by the article Managing Hierarchical Data in MySQL, however it is adding the above list as records inside the table....and I'm not sure if that is the best thing to do since I will be having multiple species per genus and multiple genus per family and so on. What would you suggest is the best way to approach this problem. Thanks in advance.

Answer 1

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    | NULL
2 | Domain  | 1
...
7 | Family  | 6
8 | Genus   | 7
9 | 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.