Таблицы, единственной целью которых является указание подмножества другой таблицы

Question

База данных, которую я разрабатываю, имеет employees Таблица; может быть несколько типов работников, одним из которых являются медицинские работники. База данных должна также описывать отношение "многие ко многим" между медицинскими работниками и их компетенциями.

Можно ли создать таблицу? medical_employees только с id столбец, чья единственная цель состоит в том, чтобы указать, какие сотрудники являются медиками id столбец имеет ограничение внешнего ключа, который ссылается на employees Таблица. Код ниже должен прояснить мой вопрос:

/* 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)
);

Answer 1

Почему отдельная таблица для этого. Почему бы просто не создать BIT/Boolean поле сказать IsMedical и установите это TRUE для медицинских работников в employee стол как

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

Таким образом, скажем, если вы хотите, чтобы все медицинские работники из Employee Таблица; вам просто нужно сделать один фильтр в WHERE условие говоря WHERE IsMedical = true, Принимая во внимание, что если вы идете за отдельной таблицей, то вам придется выполнить INNER JOIN с medical_employees а также employees Стол, который я считаю, будет более дорогостоящим и ненужным.

Answer 2

+1 к ответу @Rahul, другая альтернатива - создать атрибут в таблице сотрудников. Хотя я бы не использовал BIT, потому что в этом типе данных есть ошибки. Просто используйте BOOLEAN или TINYINT.

Но то, как вы это делаете, создавая вторую таблицу, имеет следующее преимущество: medical_employees_competences неявно ограничено ссылаться только на медицинских работников. Он не может ссылаться на кого-то, если он не находится в этой таблице.

Другой способ обеспечить это ограничение - создать внешний ключ следующим образом:

CREATE TABLE employees (
    id      INT PRIMARY KEY AUTO_INCREMENT,
    name    VARCHAR(100) NOT NULL
    IsMedical BOOLEAN DEFAULT 0,
    KEY (id, IsMedical)
);

/* A many-to-many relation between medical employees and
   their competences. */
CREATE TABLE medical_employees_competences (
    id             INT PRIMARY KEY AUTO_INCREMENT,
    IsMedical      BOOLEAN DEFAULT 1, /* only put 1 in this column */
    medic_id       INT NOT NULL,
    competence_id  INT NOT NULL,
    FOREIGN KEY (medic_id, IsMedical) REFERENCES medical_employees(id, IsMedical),
    FOREIGN KEY (competence_id) REFERENCES medical_competences(id)
);

Теперь вы можете достичь того же ограничения, что вы можете ссылаться только на медицинских работников, используя вторую таблицу.

Answer 3

Да, все в порядке, это простая реляционная идиома, и это то, что вы должны делать. (Вы можете искать по подтипам и супертипам SQL.)

Когда кто-то имеет непересекающийся подтип, например, другие типы сотрудников, в которых сотрудник может быть только одного вида, существуют SQL-идиомы, чтобы ограничить этот случай настолько декларативно, насколько это возможно. Это может включать в себя столбец дискриминатора типа константы в супертипе, описывающий, в каком единственном подтипе должен присутствовать его идентификатор. (Идиома IDEF1X.) Существует также идиома, включающая этот дискриминатор типа также в подтипы, иногда избегая дальнейших не декларативных ограничений. Для первого см. (Ответ) Как реализовать ссылочную целостность в подтипах. (Объяснение первого, хотя и пренебрежение последним.) Для последнего см. (Документ конференции) Иностранные суперключи и постоянные ссылки.