Проектирование базы данных

В одном переименованном курсе теперь две названия

• Школа копирует название курса в каждую строку записи о зачислении. Переименование некоторых копий приводит к появлению двух противоречащих друг другу названий для одного и того же курса.
• Реляционная база данных хранит строки в таблицах. Сначала определите сущности, такие как студенты и курсы, и связи между ними.

Разделите идентификатор от изменяемой метки

• Первичный ключ уникально идентифицирует строку и не может быть пустым (NULL). Стабильный ID студента позволяет изменить имя, не меняя личность студента.
• Естественный ключ может работать, если его уникальность и стабильность гарантированы. Имя — плохой ключ здесь, так как у разных студентов оно может совпадать; электронная почта также может измениться.

Представьте связь «многие ко многим»

• Один студент может посещать много курсов; один курс может иметь много студентов. Используйте таблицу зачислений с одной строкой для каждой пары студент–курс.
• Внешний ключ связывает каждую запись о зачислении с действующим ключом студента или курса. Составной первичный ключ (student_id, course_id) предотвращает дублирование одной и той же пары.

Храните факт о курсе вместе с курсом

• Нормализация организует таблицы на основе их зависимостей, чтобы уменьшить несогласованные повторения. В данном дизайне ID курса определяет его название, поэтому храните название в таблице курсов.
• Повторение значений внешних ключей ожидается: множество записей о зачислении могут ссылаться на один курс. Правило «никогда не повторяйте никаких значений» не является полезным правилом проектирования баз данных.

Включите и проверьте ограничения

• В SQLite включите принудительное выполнение внешних ключей для каждого соединения с помощью PRAGMA foreign_keys = ON до начала работы. Простого объявления связи недостаточно.
• Проверьте валидную запись, дубликат пары и запись, ссылку на отсутствующий курс. Ограничения должны отклонить последние две; они не определяют, у кого из вызывающих сторон есть права доступа.

PRAGMA foreign_keys = ON;
CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT NOT NULL);
CREATE TABLE enrolments (
  student_id INTEGER REFERENCES students(id),
  course_id INTEGER REFERENCES courses(id),
  PRIMARY KEY (student_id, course_id),
  CHECK (student_id IS NOT NULL AND course_id IS NOT NULL)
);

Проверьте проект на новом примере

• Для библиотечных займов название книги не обязательно является уникальной физической копией. Определите заемщика, конкретную копию и событие займа перед выбором ключей.
• Решите, допускаются ли повторяющиеся события. Пара студент–курс может идентифицировать одну запись здесь, но пара заемщик–копия не позволяет различить повторные займы по разным датам.