Базы данных: проектирование таблиц, связей, запросов и хранения информации
Базы данных — это организованное хранилище информации, доступ к которому идёт через систему управления (СУБД). За коротким определением стоят несколько слоёв: модель данных, структура таблиц, связи между ними, язык запросов и физический способ хранения. Решение на каждом слое проявляется не сразу, а позже — в медленных отчётах, дублях в справочнике или в данных, которые невозможно свести в одну выборку без ручной правки.
Дальше — последовательный разбор: от сущностей и таблиц к связям, запросам и выбору хранилища. Материал рассчитан на тех, кто проектирует первую базу или приводит в порядок существующую, и не требует опыта администрирования.
Что такое база данных: данные, СУБД и схема
Понятия «база данных», «СУБД» и «схема» часто смешивают, хотя обозначают разное.
- Данные — сами записи: заказы, клиенты, товары, показания датчиков, сообщения.
- СУБД — программное обеспечение, которое принимает запросы, обеспечивает целостность, разграничивает доступ и отвечает за восстановление после сбоя.
- Схема — описание структуры: таблицы, столбцы, типы, ограничения, связи.
- Приложение — код, который обращается к СУБД и показывает результат пользователю.
В реляционной модели данные лежат в таблицах: строка — один объект (например, один заказ), столбец — его свойство (дата, сумма, статус). Строки различаются по ключу.
Первичный ключ однозначно идентифицирует строку внутри таблицы. Внешний ключ — столбец, который ссылается на первичный ключ другой таблицы и фиксирует связь. Ключ бывает естественным (код страны, ISBN) и суррогатным — автоматически присвоенным номером или UUID. Суррогатный ключ удобен тем, что не меняется вместе со значением: при смене названия компании её идентификатор остаётся прежним.
Полезно сразу договориться о соглашениях: как называются таблицы и столбцы, в каком регистре, в единственном или множественном числе. Это не косметика — единые правила заметно упрощают чтение запросов и работу в команде.
Отдельный вопрос — где заканчивается файл и начинается база данных. Электронная таблица тоже хранит табличные данные, и для расчётов в одном файле её часто достаточно: об этом подробнее в материале про построение рабочих таблиц в Excel. Но когда с данными работают несколько человек одновременно, нужны права доступа, история изменений и устойчивость к сбоям — переход к СУБД становится вопросом надёжности, а не моды.
Проектирование таблиц: от сущностей к структуре
Проектирование начинается не с SQL, а с описания предметной области обычными словами: какие объекты важны, какие у них свойства, как они связаны. Только после этого появляются таблицы.
- Выписать сущности — объекты, о которых нужно хранить данные: клиент, заказ, товар, платёж.
- Для каждой сущности определить атрибуты и решить, какие из них принадлежат именно ей, а какие — соседней.
- Назначить первичные ключи.
- Описать связи и то, как они выражаются через внешние ключи.
- Задать типы данных и ограничения: обязательность, уникальность, допустимые значения.
- Проверить структуру на типовых вопросах: какие выборки и отчёты понадобятся и не потребуют ли они перестроения таблиц.
Гипотетический пример: интернет-магазин. Клиент, заказ, позиция заказа, товар, платёж — несколько сущностей, из которых вырастает схема. Соблазн положить всё в одну таблицу «заказы» велик, но тогда адрес клиента, название товара и цена повторятся в каждой строке, а изменение цены задним числом испортит историю.
Отдельно продумайте, какие значения допустимы. Статус заказа — не произвольный текст, а ограниченный набор; зафиксировать его через справочник или ограничение CHECK надёжнее, чем надеяться на аккуратность кода.
Нормализация и момент, когда её достаточно
Нормализация — набор правил, которые убирают дублирование и связанные с ним аномалии обновления. В общих чертах: первая нормальная форма требует атомарных значений в ячейках, без списков через запятую; вторая — чтобы неключевые столбцы зависели от всего первичного ключа, а не от его части; третья — чтобы неключевые столбцы не зависели друг от друга.
На практике третьей формы хватает для большинства прикладных задач. Дальше начинается осознанная денормализация: например, в отчётную таблицу специально кладут уже посчитанные суммы, чтобы не пересчитывать их при каждом открытии страницы. Ключевое слово здесь — осознанная: у такого решения должна быть причина, и лучше, если она записана рядом со схемой.
Проверить структуру можно простым вопросом: если при обновлении одного факта приходится править несколько строк в разных местах, скорее всего, данные продублированы.
Типы данных и ограничения
Тип данных определяет, сколько места занимает значение, какие операции с ним допустимы и как СУБД проверяет корректность. Дата должна храниться как дата, а не как строка: иначе сортировка пойдёт по алфавиту, а разница между двумя датами не посчитается без преобразований.
Ограничения переносят часть проверок из кода в саму базу: NOT NULL запрещает пустое значение, UNIQUE — дубли, CHECK задаёт диапазон или список, внешний ключ не даёт сослаться на несуществующую запись. Это не заменяет валидацию в приложении, но создаёт последний рубеж, который трудно обойти случайным скриптом.
Практическое правило: чем раньше поймана ошибка, тем дешевле исправление. Ограничение на уровне схемы срабатывает в момент записи, а не через месяц в отчёте.
Связи между таблицами: три базовых типа
Связи описывают, как записи одной таблицы соотносятся с записями другой. В реляционной модели их три, и почти любая реальная схема собирается из них.
| Тип связи | Как выглядит на практике | Как реализуется |
|---|---|---|
| Один к одному | У клиента ровно один профиль настроек | Отдельная таблица, связанная по уникальному ключу |
| Один ко многим | У клиента несколько заказов, у заказа один клиент | Внешний ключ в таблице «многие» |
| Многие ко многим | У товара несколько тегов, у тега несколько товаров | Связующая таблица с двумя внешними ключами |
Связь «многие ко многим» напрямую не выражается: нужна промежуточная таблица, где каждая строка — пара идентификаторов. Например, товар и тег: таблица product_tags хранит product_id и tag_id, а сама пара часто становится составным первичным ключом, который заодно не даёт поставить один и тот же тег дважды.
Связь «один к одному» встречается реже и обычно служит для разделения тяжёлых или необязательных данных: основная карточка клиента в одной таблице, расширенные настройки — в другой.
Каскадные действия — удаление или обновление связанных строк вместе с родительской записью — экономят время, но требуют осторожности: неверно настроенный каскад способен удалить больше, чем предполагалось. Для исторических данных (заказы, платежи) чаще выбирают запрет удаления, а не каскад.
Проверка связей перед запуском: для каждой таблицы понятно, что произойдёт при удалении родительской записи; нет внешних ключей, ссылающихся на несуществующие строки; нет столбцов, которые дублируют чужие данные «на всякий случай».
Запросы: как сформулировать вопрос к данным
SQL описывает, какой результат нужен, а не как его получить — порядок действий выбирает СУБД. Каркас запроса состоит из нескольких частей, и их полезно держать в голове по порядку.
- SELECT — какие столбцы попадут в результат.
- FROM и JOIN — из каких таблиц берутся данные и по какому условию они соединяются.
- WHERE — какие строки нужны.
- GROUP BY и HAVING — как сгруппировать строки и какие группы оставить.
- ORDER BY и LIMIT — в каком порядке выдать результат и сколько строк показать.
Соединения — место, где чаще всего появляются неожиданные результаты. Если соединить две таблицы по неключевому текстовому полю, можно получить размножение строк: одна запись слева совпадёт с несколькими справа. Проверка простая — сравнить количество строк до и после соединения.
Агрегаты COUNT, SUM, AVG и другие считают сводные значения по группе. Типичная ошибка новичка — фильтр по агрегату в WHERE; для этого есть HAVING, потому что WHERE применяется к строкам до группировки.
Для повторяющихся отчётов удобны представления (views) и общие табличные выражения (CTE): они позволяют разбить сложный запрос на именованные шаги и не дублировать один и тот же подзапрос несколько раз.
Индексы. Индекс — вспомогательная структура, которая ускоряет поиск по столбцу, но занимает место и замедляет вставку и обновление, потому что его тоже нужно поддерживать в актуальном состоянии. Поэтому индексы ставят под конкретные запросы: по полям в условиях соединения и фильтрации, а не «на все столбцы сразу».
Чек-лист перед тем, как отдать запрос в рабочую систему:
- Понятно, что считается одной строкой результата.
- Соединения идут по ключам, а не по «похожим» текстовым полям.
- Фильтры по строкам стоят в WHERE, по агрегатам — в HAVING.
- Для регулярных запросов проверен план выполнения.
- Результат ограничен, если данные нужны для просмотра, а не для полной выгрузки.
Когда запросов становится много, а нагрузка растёт, на первый план выходят резервные копии, мониторинг и управление правами — это уже область администрирования баз данных.
Способы хранения информации: что выбрать под задачу
Реляционная СУБД — распространённый, но не единственный способ хранить данные. Выбор зависит от формы данных, характера нагрузки и того, насколько строго нужно соблюдать связи.
| Способ хранения | Когда подходит | На что обратить внимание |
|---|---|---|
| Реляционная СУБД | Структурированные данные со связями, отчётность, транзакции | Нужна продуманная схема; изменение структуры требует миграций |
| Документная база | Объекты разной формы, которые удобно хранить целиком | Контроль дублей и связей во многом ложится на приложение |
| Хранилище «ключ — значение» | Быстрый доступ по ключу: кэш, сессии, счётчики | Сложные выборки по нескольким полям неудобны |
| Колоночная база | Аналитика по большим массивам с агрегатами | Точечные обновления отдельных записей менее удобны |
| Графовая база | Сильно связанные данные: маршруты, цепочки связей | Избыточна, если связей немного |
| Файлы и объектное хранилище | Изображения, документы, архивы | Метаданные всё равно удобнее держать в базе |
На практике выбор редко бывает «или-или». Метаданные и транзакционные данные удобно держать в реляционной базе, файлы — в объектном хранилище, а горячие счётчики — в быстром хранилище ключ-значение. Важно, чтобы у каждой части был один источник истины: если одни и те же сведения хранятся в двух местах, рано или поздно они разойдутся.
Ещё один критерий — то, как данные будут читать. Если преобладают агрегаты за большие периоды, важна не столько скорость одной записи, сколько скорость сканирования и группировки. Если важнее точечные операции по ключу, решает уже другой набор свойств. Оценить распределения и связи в данных помогает статистический анализ, и его результаты часто влияют на схему.
И последнее: схема живёт дольше, чем кажется. Миграции — изменение структуры на работающей базе — лучше планировать заранее. Добавить столбец обычно проще, чем переименовать или удалить его, если с ним уже работает код.
Частые вопросы
С чего начать проектирование базы данных?
С описания предметной области словами: какие объекты важны, какие у них свойства и как они связаны. Затем объекты превращаются в таблицы, свойства — в столбцы, а связи — во внешние ключи. SQL пишется последним.
Обязательно ли приводить таблицы к третьей нормальной форме?
Для большинства прикладных задач третьей формы достаточно: она убирает основное дублирование. Дальше возможна осознанная денормализация ради скорости отчётов, но у неё должна быть причина и понятная цена — например, необходимость синхронизировать продублированные значения.
Чем реляционная база данных отличается от документной?
В реляционной данные разложены по таблицам с фиксированными столбцами и связями через ключи. В документной объект хранится целиком, и его структура может отличаться от записи к записи. Первая удобнее для строгих связей и отчётности, вторая — для разнородных объектов, но контроль дублей и связей в ней во многом ложится на приложение.
Зачем нужны индексы и сколько их создавать?
Индекс ускоряет поиск и соединение по столбцу, но занимает место и замедляет вставку и обновление. Универсального числа нет: индексы создают под конкретные запросы и проверяют по плану выполнения, а неиспользуемые — удаляют.
Что такое первичный и внешний ключ?
Первичный ключ уникально идентифицирует строку в таблице. Внешний ключ — столбец, который ссылается на первичный ключ другой таблицы и фиксирует связь; он же не даёт сослаться на несуществующую запись.
Можно ли хранить файлы прямо в базе данных?
Технически можно, но крупные файлы обычно выносят в объектное хранилище, а в базе держат метаданные и ссылку. Это упрощает резервное копирование и снижает нагрузку на саму базу.