Как ты решаешь связь многие ко многим?

Решение связей многие-ко-многим (Many-to-Many)

Связь многие-ко-многим (M2M) — одна из самых сложных в работе Data Analyst при проектировании БД и анализе данных. Например, студенты могут изучать несколько курсов, а курсы могут быть у нескольких студентов. Решение этой проблемы зависит от контекста и инструментов.

1. Нормализация БД: Таблица соединения (Junction Table)

Это классический и правильный подход в реляционных БД:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    duration_days INT
);

CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    completion_status VARCHAR(20),
    grade DECIMAL(3,2),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Ключевые особенности:

• Первичный ключ — составной (student_id + course_id)
• Хранит дополнительные атрибуты связи (дата, статус, оценка)
• Предотвращает дублирование студентов и курсов
• Обеспечивает целостность данных

2. Запросы к M2M структуре

Получить курсы студента

SELECT c.course_name, sc.enrollment_date, sc.grade
FROM student_courses sc
JOIN courses c ON sc.course_id = c.course_id
WHERE sc.student_id = 5;

Анализ студентов на двух курсах

SELECT DISTINCT s.student_id, s.name
FROM students s
WHERE EXISTS (
    SELECT 1 FROM student_courses sc1 
    WHERE sc1.student_id = s.student_id 
    AND sc1.course_id = 1
)
AND EXISTS (
    SELECT 1 FROM student_courses sc2 
    WHERE sc2.student_id = s.student_id 
    AND sc2.course_id = 2
);

3. Работа с M2M в Python/pandas

При анализе данных часто нужно развернуть M2M в удобную форму:

import pandas as pd

student_courses = pd.DataFrame({
    'student_id': [1, 1, 1, 2, 2, 3, 3, 3],
    'course_id': [101, 102, 103, 101, 102, 102, 103, 104],
    'grade': [4.5, 3.8, 4.2, 4.0, 3.9, 4.8, 4.1, 3.7]
})

# Матрица студент-курс
pivot_matrix = student_courses.pivot_table(
    index='student_id',
    columns='course_id',
    values='grade',
    fill_value=0
)
print(pivot_matrix)

4. Обычные ошибки Data Analyst

Ошибка 1: Повторное соединение (Double counting)

-- НЕПРАВИЛЬНО: если один студент на 3 курсах, умножится
SELECT COUNT(DISTINCT s.student_id)
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id
JOIN courses c ON sc.course_id = c.course_id;

-- ПРАВИЛЬНО
SELECT COUNT(DISTINCT s.student_id)
FROM students s
JOIN student_courses sc ON s.student_id = sc.student_id;

Ошибка 2: Агрегация на неправильном уровне

-- Правильно считать среднюю оценку по студентам
SELECT s.student_id, AVG(sc.grade) as avg_grade
FROM student_courses sc
JOIN students s ON sc.student_id = s.student_id
GROUP BY s.student_id;

5. Рекомендации

1. Всегда нормализуйте M2M через junction table в реляционных БД
2. Проверяйте double counting при множественных JOIN'ах
3. Используйте DISTINCT когда нужны уникальные сущности
4. Тестируйте на реальных данных перед боевым запуском
5. Добавляйте индексы на оба направления связи

Для Data Analyst: M2M — это не просто структура БД, но и источник ошибок в анализе. Всегда проверяйте количество строк до и после JOIN'а, убедитесь что не считаете одну метрику несколько раз.