分类表 mysql,在MySQL中创建分类表

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.

解决方案

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.