Создание связей "многие ко многим" через промежуточную таблицу

Почему "многие ко многим" — это мощно 💪

Представьте библиотеку: одна книга может принадлежать нескольким авторам, а один автор может написать много книг. Или соцсеть: пользователь состоит в множестве групп, а в группе много пользователей. Это и есть связь "многие ко многим" (Many-to-Many) — фундамент сложных систем!

Но SQL не умеет хранить такие связи напрямую. Решение? Промежуточная таблица — элегантный мостик между сущностями.

Как работает промежуточная таблица?

Возьмем пример с курсами и студентами: - Один курс посещают много студентов - Один студент может быть на многих курсах

Создадим три таблицы:

-- Основные сущности
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    title VARCHAR(100) NOT NULL
);

-- Промежуточная таблица-мост
CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

🔑 Ключевые моменты: - Составной PRIMARY KEY из двух внешних ключей - FOREIGN KEY обеспечивают целостность данных - Можно добавлять дополнительные поля (как enrollment_date)

Реальные примеры запросов 🔥

Добавление связи

-- Студент с ID 1 записывается на курс с ID 3
INSERT INTO student_courses (student_id, course_id, enrollment_date)
VALUES (1, 3, '2023-09-15');

Поиск всех курсов студента

SELECT c.title
FROM courses c
JOIN student_courses sc ON c.course_id = sc.course_id
WHERE sc.student_id = 1;

Количество студентов на курсе

SELECT c.title, COUNT(sc.student_id) as students_count
FROM courses c
LEFT JOIN student_courses sc ON c.course_id = sc.course_id
GROUP BY c.course_id;

Лайфхаки для профессионалов ️

1. Индексы для скорости:

CREATE INDEX idx_student ON student_courses(student_id);
CREATE INDEX idx_course ON student_courses(course_id);

2. Каскадное удаление (осторожно!):

FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE

3. Уникальные комбинации:

-- Чтобы студент не мог записаться на курс дважды
ALTER TABLE student_courses ADD CONSTRAINT unique_pair UNIQUE (student_id, course_id);

Типичные ошибки и как их избежать 🚨

1. Отсутствие первичного ключа в промежуточной таблице → дубликаты записей
2. Неправильные JOIN → потеря данных или "раздувание" результата
3. Игнорирование транзакций при сложных операциях

Пример безопасного обновления:

BEGIN TRANSACTION;

DELETE FROM student_courses WHERE student_id = 5;
INSERT INTO student_courses (student_id, course_id) VALUES (5, 2), (5, 7);

COMMIT;

Практикуйтесь на реальных сценариях!

Попробуйте смоделировать:

1. Систему тегов для блога (посты ↔ теги)
2. Меню ресторана (блюда ↔ ингредиенты)
3. Расписание врачей (врачи ↔ пациенты)