数据库设计：如何存储已知属但未知种的植物信息？

最优方案：统一分类单元层级表 #

创建一个taxonomic_units表存储所有分类层级（属、种），通过父子关系构建层级结构，让植物可以关联到任意确定的分类单元（属或种），完美解决仅知属未知种的场景。

表结构代码 #

-- 分类单元表：存储属、种等分类层级
CREATE TABLE taxonomic_units (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    rank TEXT NOT NULL CHECK(rank IN ('genus', 'species')), -- 限定分类等级为属或种
    parent_id INTEGER REFERENCES taxonomic_units(id), -- 父分类ID：种的父是对应属，属无父
    UNIQUE(name, rank) -- 同等级下名称唯一（如属名唯一，同属内种名唯一）
);

-- 植物表：关联到分类单元
CREATE TABLE plants (
    id INTEGER PRIMARY KEY,
    taxon_id INTEGER NOT NULL REFERENCES taxonomic_units(id),
    -- 其他植物属性字段（如种植位置、获取日期等）...
);

数据操作示例 #

• 插入属（蝴蝶兰属Phalaenopsis）：

INSERT INTO taxonomic_units (name, rank, parent_id) VALUES ('Phalaenopsis', 'genus', NULL);

• 插入该属下的已知种（白花蝴蝶兰Phalaenopsis amabilis）：

INSERT INTO taxonomic_units (name, rank, parent_id) VALUES ('amabilis', 'species', 1); -- 假设属的ID为1

• 插入仅知属的植物：

INSERT INTO plants (taxon_id) VALUES (1); -- 关联到Phalaenopsis属

• 插入已知种的植物：

INSERT INTO plants (taxon_id) VALUES (2); -- 关联到Phalaenopsis amabilis种

方案核心优势 #

• 无冗余数据：无需为每个属创建“未知种”这类冗余记录
• 强数据一致性：通过CHECK约束和外键保证分类层级的合法性，不会出现属种不匹配的情况
• 高扩展性：后续如需添加科、目等更高阶分类，只需扩展rank的可选值即可

备选方案：基于原结构的约束优化 #

如果不想重构分类表，可修改plants表，通过约束保证属、种关联的一致性：

CREATE TABLE genuses (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE species (
    id INTEGER PRIMARY KEY,
    genus_id INTEGER NOT NULL REFERENCES genuses(id),
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE plants (
    id INTEGER PRIMARY KEY,
    genus_id INTEGER NOT NULL REFERENCES genuses(id),
    species_id INTEGER REFERENCES species(id),
    -- 约束：若填写种ID，必须与属ID匹配
    CHECK (species_id IS NULL OR (SELECT genus_id FROM species WHERE id = species_id) = genus_id),
    -- 约束：确保种ID为空时仅关联属，非空时属ID已正确填写
    CHECK (species_id IS NULL OR genus_id IS NOT NULL)
);

备选方案优势 #

• 改动量小：基于原有表结构调整，无需大规模重构
• 解决一致性问题：通过CHECK约束替代触发器，避免数据不一致
• 直接支持目标场景：仅知属时将species_id设为NULL即可

内容的提问来源于stack exchange，提问作者axblount