唯一目的是指定另一个表的子集的表 [英] Tables whose sole purpose is specify a subset of another table

问题描述

我正在设计的数据库有一个 employees 表；可以有多种类型的员工，其中一种是医疗员工.数据库还需要描述医务人员与其具备的能力之间的多对多关系.

The database I'm designing has an employees table; there can be multiple types of employees, one of which are medical employees. The database needs to also describe a many-to-many relation between medical employees and what competences they have.

是否可以创建一个只有 id 列的表 medical_employees，其唯一目的是指定哪些员工是医生?id 列有一个引用 employees 表的外键约束.下面的代码应该让我的问题更清楚:

Is it okay to create a table medical_employees with only an id column, whose only purpose is to specify which employees are medics? The id column has a foreign key constraint that references the employees table. The code below should make my question clearer:

/* Defines a generic employee */
CREATE TABLE employees (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    name    VARCHAR(100) NOT NULL
);

/* Specifies which employees are medics */
CREATE TABLE medical_employees (
    id INT NOT NULL,
    FOREIGN KEY (id) references employees(id);
);

/* Specifies what competences a medic can have */
CREATE TABLE medical_competences (
    id     INT PRIMARY KEY AUTO_INCREMENT,
    name   VARCHAR(100) NOT NULL
);

/* A many-to-many relation between medical employees and
   their competences. */
CREATE TABLE medical_employees_competences (
    id             INT PRIMARY KEY AUTO_INCREMENT,
    medic_id       INT NOT NULL,
    competence_id  INT NOT NULL,
    FOREIGN KEY (medic_id) REFERENCES medical_employees(id),
    FOREIGN KEY (competence_id) REFERENCES medical_competences(id)
);

推荐答案

是的，没关系，这是直接的关系习语，也是您应该做的.(您可以搜索 SQL 子类型和超类型.)

Yes it is ok, it is the straightforward relational idiom and it is what you should do. (You can search on SQL subtypes & supertypes.)

当一个人有不相交的子类型时，例如其他类型的员工，其中一个员工只能是一种，有 SQL 习语可以尽可能声明性地限制这种情况.这可能涉及超类型中的常量类型鉴别器列，描述其 id 应出现在哪个唯一子类型中.(IDEF1X 成语.)还有一个成语涉及该类型鉴别器也在子类型中有时会避免进一步的非声明性约束.对于前者，请参阅(答案)如何在子类型中实现参照完整性.(解释前者但贬低后者.)对于后者，请参见(会议论文)外部超级键和常量引用.

When one has disjoint subtyping, eg other kinds of employees where an employee can only be of one kind, there are SQL idioms for constraining that to be the case as declaratively as possible. This can involve a constant type discriminator column in the supertype describing which sole subtype its id should appear in. (The IDEF1X idiom.) There is also an idiom involving that type discriminator also in subtypes sometime avoidig further non-declarative constraints. For the former see (answer) How to Implement Referential Integrity in Subtypes. (Explaining the former although disparaging the latter.) For the latter see (conference paper) Foreign Superkeys and Constant References.

这篇关于唯一目的是指定另一个表的子集的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！