Создание схемы БД для учителей и учебных программ

Условие

В организации есть преподаватели и учебные программы, по которым они обучают студентов. Один преподаватель может работать в нескольких программах, а в каждой программе может участвовать несколько преподавателей. У преподавателя хранятся два атрибута: ФИО и должность, у программы — единственное поле — название. Требуется спроектировать и реализовать базу данных, содержащую две таблицы: teachers и programs, а также обеспечить связь «многие‑ко‑многим» между ними.

Ответ

Для нормализованной связи многие-ко-многим помимо teachers и programs нужна третья связующая таблица teacher_program с уникальной парой идентификаторов.

CREATE TABLE teachers (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name text NOT NULL,
    position text NOT NULL
);
CREATE TABLE programs (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL
);
CREATE TABLE teacher_program (
    teacher_id bigint NOT NULL REFERENCES teachers(id),
    program_id bigint NOT NULL REFERENCES programs(id),
    PRIMARY KEY (teacher_id, program_id)
);
CREATE INDEX teacher_program_program_idx ON teacher_program(program_id);

Это пример PostgreSQL. Одного внешнего ключа в одной из двух основных таблиц недостаточно: он дал бы связь один-ко-многим. Массивы идентификаторов или строки со списками ухудшают целостность. Правила удаления связанных записей следует выбрать отдельно, не добавляя каскадное удаление автоматически.