В эпизоде разбираем одну из самых популярных задач для собеседования на системного аналитика: проектирование БД, ER-диаграмма и связь “многие ко-многим”.

Эпизод будет полезен для всех начинающих и опытных системных аналитиков, у кого мало опыта в создании ER-диаграмм с нуля.

Таймкоды эпизода

Введение

00:18 - Про актуальность задачи проектирования БД и работы с ER-диаграммами системных аналитиков.
02:08 - Обзор вариантов задач, первичная аналитика по выбранной задаче и правильное использование инструмента draw.io для создания ER-диаграмм.

Этап 1. Создание каркаса ER-диаграммы
05:00 - Первые шаги в создании ER-диаграммы - сущности и свойства. СУБД PostgreSQL для определения типов данных.
15:00 - Внешние ключи (FK - Foreign Key). Установка связей между таблицами по id сущностей.
16:28 - Структурирование данных при хранение в БД (Адрес + ФИО аналогично).
18:10 - Установка кратностей связей для отношений между таблицами. Понимание внешних ключей (FK).

Этап 2. Представление ER-диаграммы в виде таблиц EXCEL

20:30 - Представление данных в EXCEL: как таблицы в ER-диаграмме связаны с реальным хранением данных.
27:22 - Как найти связь “многие-ко-многим” и избавиться от неё.
30:03 - Зачем убирать связь “многие-ко-многим” и как она выглядит при работе с реальными данными.

Этап 3. ER-диаграмма после добавления промежуточной таблицы, чтобы убрать связь "многие-ко-многим"

32:59 - Промежуточная таблица для связи “многие-ко-многим”: как её добавить и что происходит с кратностями и внешними ключами.
41:05 - Исторические данные в БД с классическим примером по Интернет-магазину.

Разбор дополнительных задач, чтобы “набить руку”

43:26 - Почему задача важна и как её могут непредсказуемо усложнять и развивать в процессе.
44:27 - Пример простой задачи по медицинской системе.
47:23 - Усложненная задача по агентству недвижимости.
56:43 - Подведение итогов и рекомендации.

ER-диаграммы к эпизоду

Задача 1. Интернет-магазин

Есть интернет-магазин, в котором пользователи могут заказывать различные товары. Опишите, как организовать хранение данных в базе данных для учета пользователей, товаров и заказов.

Первичный ключ {PK} id в промежуточной таблице Заказ_Товар можно убрать, он не обязателен для промежуточной таблицы и скорее избыточен, что нарушает требования к НФ в БД.

Но в реальной практике это не является ошибкой, т.к. может быть введено требование о запрете на составной первичный ключ для всех таблиц, а также могут быть дополнительные связанные таблицы с Заказ_Товар, когда для ссылки на неё будет важно иметь один системный первичный ключ.

Задача 2. Медицинская система

В медицинской системе пациенты могут посещать врачей. Опишите, как организовать хранение данных для учета пациентов и их посещений.

Первичный ключ {PK} id в промежуточной таблице можно убрать - не обязателен и может быть избыточным.
Проговаривается в подкасте для примера с Интернет-магазином.

Задача 3. Агентство недвижимости

В агентстве недвижимости агенты могут предлагать разные объекты недвижимости потенциальным покупателям. Есть ограничение, что для каждого агента возможность показа ограничивается только его списком объектов. Но при этом один объект может быть закреплен максимум за 3 агентами. 

Опишите, как организовать хранение данных в базе данных для учета агентов и объектов недвижимости.

Дополнительные задачи

Задача 4.

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

Предложите структуру базы данных. Объясните, какие таблицы вы создадите, как они будут связаны между собой, и почему именно такая структура выбрана.

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

Задача 5.

В музыкальном сервисе пользователи могут сохранять треки в свои личные плейлисты. 

Опишите структуру хранения данных для такого сервиса. 
Показать: пользователей, треки и их плейлисты.

Могут быть использованы дополнительные таблицы.

Рекомендации по дополнительным материалам

Файлы к эпизоду для загрузки

Следите за анонсами подкаста в Telegram!