表的唯一目的是指定另一个表的子集

问题描述

我正在设计的数据库有一个员工表;可以有多种类型的员工，其中一类是医务人员。数据库还需要描述医务人员之间的多对多关系以及他们拥有的能力。

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 列具有引用员工表的外键约束。下面的代码应该使我的问题更清楚：

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语句的SQL语言尽可能的声明性。这可能涉及超类型中的常量类型鉴别列，描述其id应该出现在哪个唯一子类型中。（成语））还有一个成语涉及该类型鉴别器也在子类型有时避免进一步的非声明性约束。对于前者，请参阅（回答）。 （解释前者虽然贬低后者）对于后者（见会议论文）。

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.

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