在MySQL中创建分类表 [英] Creating Taxonomy Table in MySQL

问题描述

我创建了一个植物数据库，植物将按照其分类法进行组织：

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

生活
域名
王国
Phylum
类
订单
家庭
种属
种类

Life Domain Kingdom Phylum Class Order Family Genus Species

我正在考虑使用文章管理MySQL中的分层数据，但它添加了上面的列表作为表内的记录....我不知道如果这是最好的事情，因为我将有多个物种每个属和多个属每个家庭等。你会建议什么是解决这个问题的最佳方法。先感谢。

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.

推荐答案

我使用类似的数据，我做了2部分。在PostgreSQL语法中。

I worked with similar data, and I made it in 2 parts. In PostgreSQL syntax.

首先是分类结构（Family，Genus，Species，...）：

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.

这篇关于在MySQL中创建分类表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！