Моделирование связей между таблицами для нормализации

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

Если базы данных — это организованная система хранения информации, то связи между таблицами — это логические мостики, которые показывают, как данные взаимодействуют друг с другом. Представьте библиотеку, где информация о книгах хранится отдельно от информации об авторах, но при этом каждая книга "знает" своего автора через специальную связь. Или возьмите интернет-магазин: данные о товарах существуют независимо от информации о покупателях, но когда кто-то делает заказ, система связывает конкретного покупателя с конкретными товарами через таблицу заказов.

В медицинской клинике пациенты связаны со своими медицинскими картами, врачи — с расписанием приёмов, а лекарства — с назначениями. Эти связи помогают системе понимать, какая информация к чему относится, не дублируя данные без необходимости.

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

Один-к-одному (1:1)

Это связь, в которой одна запись в таблице "А" соответствует строго одной записи в таблице "Б". Например, у нас есть таблицы "Сотрудники" и "Паспортные данные". Один сотрудник может иметь только один паспорт, и каждый паспорт принадлежит единственному сотруднику.

Пример:

Сотрудники

Паспортные данные

Здесь связь осуществляется через внешний ключ сотрудник_id, который указывает на id в таблице "Сотрудники".

Один-ко-многим (1:N)

Это наиболее распространённый тип связи. Здесь каждая запись из таблицы "А" может быть связана с несколькими записями в таблице "Б", но каждая запись из таблицы "Б" связана только с одной записью из таблицы "А". Например, у нас есть таблицы "Преподаватели" и "Курсы". Один преподаватель может вести несколько курсов.

Пример:

Преподаватели

Курсы

Связь создаётся через внешний ключ преподаватель_id в таблице "Курсы".

Многие-ко-многим (M:N)

Когда у вас много-много всего, это весело, но сложно. Здесь каждая запись в таблице "А" может быть связана с несколькими записями из таблицы "Б", и наоборот. Например, студенты могут записываться на несколько курсов, а каждый курс может посещать несколько студентов.

Пример:

Студенты

Курсы

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

Записи

Моделирование связей с помощью внешних ключей

Внешний ключ — это колонка (или набор колонок), которая указывает на колонку первичного ключа в другой таблице. Это основа для построения связей между таблицами.

Пример внешнего ключа:

CREATE TABLE Курсы (
    id SERIAL PRIMARY KEY,
    название VARCHAR(255)
);

CREATE TABLE Записи (
    id SERIAL PRIMARY KEY,
    студент_id INT,
    курс_id INT,
    FOREIGN KEY (курс_id) REFERENCES Курсы(id)
);

Как избежать ошибок при проектировании внешних ключей? Прежде всего, важно проверить, чтобы типы данных между столбцами внешнего и первичного ключа совпадали — иначе база просто не позволит создать связь. А ещё стоит заранее продумать, что должно происходить при удалении записей. Например, если вы удаляете строку из родительской таблицы, как быть с дочерними записями? Один из популярных вариантов — использовать ON DELETE CASCADE, чтобы связанные данные автоматически удалялись вместе с основной записью. Это помогает поддерживать порядок и избежать «висячих» ссылок.

Реализация связи "Многие-ко-многим"

Возьмём пример: у нас есть студенты и курсы. Один студент может быть записан на несколько курсов, а на один курс может быть записано несколько студентов. Для реализации связи M:N создадим три таблицы: Студенты, Курсы и Записи.

CREATE TABLE Студенты (
    id SERIAL PRIMARY KEY,
    имя VARCHAR(255)
);

CREATE TABLE Курсы (
    id SERIAL PRIMARY KEY,
    название VARCHAR(255)
);

CREATE TABLE Записи (
    id SERIAL PRIMARY KEY,
    студент_id INT,
    курс_id INT,
    FOREIGN KEY (студент_id) REFERENCES Студенты(id),
    FOREIGN KEY (курс_id) REFERENCES Курсы(id)
);

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

Практическое задание

Создайте структуру базы данных для системы управления курсами. У вас должны быть таблицы Студенты, Курсы и Записи. Реализуйте все связи между таблицами. Затем внесите примерные данные о студентах, курсах и их записях. Давайте посмотрим, как это нужно делать.

1. Создаём таблицы:

CREATE TABLE Студенты (
    id SERIAL PRIMARY KEY,
    имя VARCHAR(255)
);

CREATE TABLE Курсы (
    id SERIAL PRIMARY KEY,
    название VARCHAR(255)
);

CREATE TABLE Записи (
    id SERIAL PRIMARY KEY,
    студент_id INT,
    курс_id INT,
    FOREIGN KEY (студент_id) REFERENCES Студенты(id),
    FOREIGN KEY (курс_id) REFERENCES Курсы(id)
);

2. Вставляем данные:

INSERT INTO Студенты (имя) VALUES ('Отто Лин'), ('Мария Чи');
INSERT INTO Курсы (название) VALUES ('Основы SQL'), ('Администрирование БД');
INSERT INTO Записи (студент_id, курс_id) VALUES (1, 1), (1, 2), (2, 1);

3. Проверяем данные:

SELECT
    Студенты.имя AS студент, 
    Курсы.название AS курс
FROM Записи
JOIN Студенты ON Записи.студент_id = Студенты.id
JOIN Курсы ON Записи.курс_id = Курсы.id;

Результат:

Сложности и особенности моделирования связей

Когда вы моделируете связи между таблицами, могут возникнуть сложности, такие как:

• Ошибки при удалении данных (например, у вас есть записи в дочерней таблице, которые зависят от записи в родительской таблице).
• Производительность запросов при большом количестве данных. Связи M:N особенно "прожорливы", так как требуют дополнительных соединений.

Решить эти проблемы помогают:

• Использование индексов на внешние ключи
• Продуманная структура базы данных.
• Баланс между нормализацией и производительностью.

Мы рассмотрели моделирование связей между таблицами на очень примитивном уровне и применили это на практике, создав структуру базы данных для системы управления курсами. Хотелось бы, конечно, разобрать какой-то большой пример, но я не придумал как это сделать. Большой пример получается сложным и скучным. И толку от него будет мало. Я попробую вернуться к этому вопросу ближе к концу курса.

2

Задача

SQL SELF, 26 уровень, 0 лекция

Недоступна

Создание связи "многие-ко-многим"

Создание связи "многие-ко-многим"

Открыть