К содержимому
← Вернуться в блог

Базы данных: проектирование таблиц, связей, запросов и хранения информации

Базы данных — это организованное хранилище информации, доступ к которому идёт через систему управления (СУБД). За коротким определением стоят несколько слоёв: модель данных, структура таблиц, связи между ними, язык запросов и физический способ хранения. Решение на каждом слое проявляется не сразу, а позже — в медленных отчётах, дублях в справочнике или в данных, которые невозможно свести в одну выборку без ручной правки.

Дальше — последовательный разбор: от сущностей и таблиц к связям, запросам и выбору хранилища. Материал рассчитан на тех, кто проектирует первую базу или приводит в порядок существующую, и не требует опыта администрирования.

Что такое база данных: данные, СУБД и схема

Понятия «база данных», «СУБД» и «схема» часто смешивают, хотя обозначают разное.

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

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

Первичный ключ однозначно идентифицирует строку внутри таблицы. Внешний ключ — столбец, который ссылается на первичный ключ другой таблицы и фиксирует связь. Ключ бывает естественным (код страны, ISBN) и суррогатным — автоматически присвоенным номером или UUID. Суррогатный ключ удобен тем, что не меняется вместе со значением: при смене названия компании её идентификатор остаётся прежним.

Полезно сразу договориться о соглашениях: как называются таблицы и столбцы, в каком регистре, в единственном или множественном числе. Это не косметика — единые правила заметно упрощают чтение запросов и работу в команде.

Отдельный вопрос — где заканчивается файл и начинается база данных. Электронная таблица тоже хранит табличные данные, и для расчётов в одном файле её часто достаточно: об этом подробнее в материале про построение рабочих таблиц в Excel. Но когда с данными работают несколько человек одновременно, нужны права доступа, история изменений и устойчивость к сбоям — переход к СУБД становится вопросом надёжности, а не моды.

Проектирование таблиц: от сущностей к структуре

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

  1. Выписать сущности — объекты, о которых нужно хранить данные: клиент, заказ, товар, платёж.
  2. Для каждой сущности определить атрибуты и решить, какие из них принадлежат именно ей, а какие — соседней.
  3. Назначить первичные ключи.
  4. Описать связи и то, как они выражаются через внешние ключи.
  5. Задать типы данных и ограничения: обязательность, уникальность, допустимые значения.
  6. Проверить структуру на типовых вопросах: какие выборки и отчёты понадобятся и не потребуют ли они перестроения таблиц.

Гипотетический пример: интернет-магазин. Клиент, заказ, позиция заказа, товар, платёж — несколько сущностей, из которых вырастает схема. Соблазн положить всё в одну таблицу «заказы» велик, но тогда адрес клиента, название товара и цена повторятся в каждой строке, а изменение цены задним числом испортит историю.

Отдельно продумайте, какие значения допустимы. Статус заказа — не произвольный текст, а ограниченный набор; зафиксировать его через справочник или ограничение 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): они позволяют разбить сложный запрос на именованные шаги и не дублировать один и тот же подзапрос несколько раз.

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

Совет. Если запрос стал медленным, начните не с переписывания, а с плана выполнения (EXPLAIN или его аналог в вашей СУБД). План показывает, какие таблицы читаются полностью, а где используется индекс, — этого обычно достаточно, чтобы увидеть причину.

Чек-лист перед тем, как отдать запрос в рабочую систему:

  • Понятно, что считается одной строкой результата.
  • Соединения идут по ключам, а не по «похожим» текстовым полям.
  • Фильтры по строкам стоят в WHERE, по агрегатам — в HAVING.
  • Для регулярных запросов проверен план выполнения.
  • Результат ограничен, если данные нужны для просмотра, а не для полной выгрузки.

Когда запросов становится много, а нагрузка растёт, на первый план выходят резервные копии, мониторинг и управление правами — это уже область администрирования баз данных.

Способы хранения информации: что выбрать под задачу

Реляционная СУБД — распространённый, но не единственный способ хранить данные. Выбор зависит от формы данных, характера нагрузки и того, насколько строго нужно соблюдать связи.

Способ хранения Когда подходит На что обратить внимание
Реляционная СУБД Структурированные данные со связями, отчётность, транзакции Нужна продуманная схема; изменение структуры требует миграций
Документная база Объекты разной формы, которые удобно хранить целиком Контроль дублей и связей во многом ложится на приложение
Хранилище «ключ — значение» Быстрый доступ по ключу: кэш, сессии, счётчики Сложные выборки по нескольким полям неудобны
Колоночная база Аналитика по большим массивам с агрегатами Точечные обновления отдельных записей менее удобны
Графовая база Сильно связанные данные: маршруты, цепочки связей Избыточна, если связей немного
Файлы и объектное хранилище Изображения, документы, архивы Метаданные всё равно удобнее держать в базе

На практике выбор редко бывает «или-или». Метаданные и транзакционные данные удобно держать в реляционной базе, файлы — в объектном хранилище, а горячие счётчики — в быстром хранилище ключ-значение. Важно, чтобы у каждой части был один источник истины: если одни и те же сведения хранятся в двух местах, рано или поздно они разойдутся.

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

Осторожно с персональными данными. Если в базе хранятся имена, контакты, идентификаторы или иные сведения о людях, может применяться законодательство о защите персональных данных, в том числе GDPR, действующий в странах ЕС, включая Эстонию. Конкретные требования — основания обработки, сроки хранения, порядок удаления и уведомления — уточняйте в официальном источнике или у юриста, а не по памяти. Отдельно продумайте резервные копии: удаление записи из рабочей базы не означает, что её нет в бэкапе.

И последнее: схема живёт дольше, чем кажется. Миграции — изменение структуры на работающей базе — лучше планировать заранее. Добавить столбец обычно проще, чем переименовать или удалить его, если с ним уже работает код.

Частые вопросы

С чего начать проектирование базы данных?

С описания предметной области словами: какие объекты важны, какие у них свойства и как они связаны. Затем объекты превращаются в таблицы, свойства — в столбцы, а связи — во внешние ключи. SQL пишется последним.

Обязательно ли приводить таблицы к третьей нормальной форме?

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

Чем реляционная база данных отличается от документной?

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

Зачем нужны индексы и сколько их создавать?

Индекс ускоряет поиск и соединение по столбцу, но занимает место и замедляет вставку и обновление. Универсального числа нет: индексы создают под конкретные запросы и проверяют по плану выполнения, а неиспользуемые — удаляют.

Что такое первичный и внешний ключ?

Первичный ключ уникально идентифицирует строку в таблице. Внешний ключ — столбец, который ссылается на первичный ключ другой таблицы и фиксирует связь; он же не даёт сослаться на несуществующую запись.

Можно ли хранить файлы прямо в базе данных?

Технически можно, но крупные файлы обычно выносят в объектное хранилище, а в базе держат метаданные и ссылку. Это упрощает резервное копирование и снижает нагрузку на саму базу.