Проектирование и нормализация баз данных SQL

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

Хорошо спроектированная база данных:

Проектирование обычно включает несколько уровней:

  1. Концептуальная модель — сущности и связи без привязки к конкретной СУБД.
  2. Логическая модель — таблицы, атрибуты, ключи и нормализация.
  3. Физическая модель — типы PostgreSQL, индексы, секционирование и параметры хранения.

Содержание

Предметная область

Проектирование начинается не с команды CREATE TABLE, а с анализа предметной области.

Например, интернет-магазин может включать:

До создания схемы необходимо определить:

Пример бизнес-правил:

Такие правила затем преобразуются в таблицы, внешние ключи, ограничения и программную логику.

Сущности, атрибуты и связи

Сущность

Сущность — объект предметной области, сведения о котором необходимо хранить.

Примеры сущностей:

В реляционной базе сущность обычно представляется таблицей:

CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL,
  name text NOT NULL
);

Таблица users хранит множество пользователей, а каждая строка представляет одного пользователя.

Атрибут

Атрибут — характеристика сущности.

Для пользователя атрибутами могут быть:

В таблице атрибуты обычно представлены колонками:

CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL,
  name text NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

Связь

Связь показывает, как сущности взаимодействуют друг с другом.

Например:

Пользователь 1 ─── N Заказ

Один пользователь может иметь много заказов, но каждый заказ принадлежит одному пользователю.

В SQL такая связь создаётся с помощью внешнего ключа:

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  user_id bigint NOT NULL
    REFERENCES users(id),

  created_at timestamptz NOT NULL DEFAULT now()
);

ER-модель

ER-модель — Entity-Relationship Model — представление сущностей, их атрибутов и связей.

Основные элементы ER-модели:

Элемент Назначение
Entity Сущность предметной области
Attribute Характеристика сущности
Primary Key Уникальный идентификатор сущности
Relationship Связь между сущностями
Cardinality Количество связанных экземпляров
Optionality Обязательность связи

Упрощённая модель интернет-магазина:

users
-----
id PK
email
name

orders
------
id PK
user_id FK
status
created_at

order_items
-----------
order_id PK, FK
product_id PK, FK
quantity
unit_price

products
--------
id PK
name
price
category_id FK

categories
----------
id PK
name

Связи:

users      1 ─── N orders
orders     1 ─── N order_items
products   1 ─── N order_items
categories 1 ─── N products

Таблица order_items одновременно является дочерней таблицей заказа и связующей таблицей между заказами и товарами.

Кардинальность связей

Один к одному

Одной строке первой таблицы соответствует не более одной строки второй таблицы.

Пример: пользователь и профиль.

users 1 ─── 1 user_profiles
CREATE TABLE user_profiles (
  user_id bigint PRIMARY KEY
    REFERENCES users(id)
    ON DELETE CASCADE,

  bio text,
  birth_date date
);

user_id одновременно является первичным и внешним ключом. Поэтому у одного пользователя не может быть более одного профиля.

Другой вариант:

CREATE TABLE user_profiles (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  user_id bigint NOT NULL UNIQUE
    REFERENCES users(id)
    ON DELETE CASCADE,

  bio text
);

Ограничение UNIQUE обеспечивает отношение один к одному.

Один ко многим

Одной строке родительской таблицы может соответствовать много строк дочерней таблицы.

users 1 ─── N orders
CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  user_id bigint NOT NULL
    REFERENCES users(id),

  created_at timestamptz NOT NULL DEFAULT now()
);

Внешний ключ располагается на стороне «многие».

Многие ко многим

Каждая строка первой таблицы может быть связана с несколькими строками второй таблицы, и наоборот.

Пример: товары и категории.

products N ─── M categories

В реляционной базе такая связь реализуется через промежуточную таблицу:

CREATE TABLE product_categories (
  product_id bigint NOT NULL
    REFERENCES products(id)
    ON DELETE CASCADE,

  category_id bigint NOT NULL
    REFERENCES categories(id)
    ON DELETE CASCADE,

  PRIMARY KEY (product_id, category_id)
);

Составной первичный ключ запрещает повторное добавление одной и той же связи.

Если связь имеет собственные атрибуты, они также размещаются в промежуточной таблице:

CREATE TABLE product_categories (
  product_id bigint NOT NULL
    REFERENCES products(id)
    ON DELETE CASCADE,

  category_id bigint NOT NULL
    REFERENCES categories(id)
    ON DELETE CASCADE,

  assigned_at timestamptz NOT NULL DEFAULT now(),
  position integer,

  PRIMARY KEY (product_id, category_id)
);

Обязательные и необязательные связи

Обязательность связи обычно выражается через NOT NULL.

Обязательная связь:

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  user_id bigint NOT NULL
    REFERENCES users(id)
);

Каждый заказ должен принадлежать пользователю.

Необязательная связь:

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  manager_id bigint
    REFERENCES employees(id)
);

manager_id может быть NULL, если менеджер ещё не назначен.

Следует отличать:

NULL

от специального значения:

0

или строки:

"не назначен"

NULL означает отсутствие известного значения или неприменимость атрибута. Значения 0 и "не назначен" являются обычными данными и не должны использоваться как универсальная замена NULL.

Выбор первичного ключа

Первичный ключ — PRIMARY KEY — колонка или набор колонок, однозначно идентифицирующий строку.

Первичный ключ:

Естественный ключ

Естественный ключ имеет смысл в предметной области.

Примеры:

CREATE TABLE countries (
  code char(2) PRIMARY KEY,
  name text NOT NULL
);

Преимущество естественного ключа — он уже существует в предметной области.

Недостатки:

Суррогатный ключ

Суррогатный ключ создаётся специально для базы данных и не имеет самостоятельного бизнес-смысла.

CREATE TABLE products (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku text NOT NULL UNIQUE,
  name text NOT NULL
);

Здесь:

Такой подход позволяет сохранить бизнес-уникальность через UNIQUE, но использовать компактный и стабильный идентификатор для связей.

Числовой идентификатор

id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY

Преимущества:

UUID

CREATE TABLE users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email text NOT NULL UNIQUE
);

UUID удобен, когда идентификаторы создаются независимо в нескольких системах или не должны быть легко перебираемыми.

Преимущества UUID:

Недостатки:

Альтернативные и составные ключи

Альтернативный ключ

Атрибут может не быть первичным ключом, но всё равно должен оставаться уникальным:

CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE
);

email является альтернативным ключом.

Составной ключ

Составной ключ состоит из нескольких колонок:

CREATE TABLE order_items (
  order_id bigint NOT NULL
    REFERENCES orders(id),

  product_id bigint NOT NULL
    REFERENCES products(id),

  quantity integer NOT NULL,

  PRIMARY KEY (order_id, product_id)
);

Комбинация order_id и product_id уникальна, хотя каждое значение отдельно может повторяться.

Составные ключи удобны для:

Если одна и та же позиция товара может встречаться в заказе несколько раз, потребуется отдельный идентификатор:

CREATE TABLE order_items (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  order_id bigint NOT NULL
    REFERENCES orders(id),

  product_id bigint NOT NULL
    REFERENCES products(id),

  quantity integer NOT NULL
);

Выбор зависит от правил предметной области.

Ограничения целостности

База данных должна защищать важные правила независимо от кода приложения.

NOT NULL

Запрещает отсутствие значения:

name text NOT NULL

UNIQUE

Запрещает повторение значения или комбинации значений:

email text NOT NULL UNIQUE

Составная уникальность:

CONSTRAINT bookings_room_period_key
  UNIQUE (room_id, booking_date, start_time)

CHECK

Проверяет условие:

price numeric(12, 2) NOT NULL
  CHECK (price >= 0)
quantity integer NOT NULL
  CHECK (quantity > 0)
status text NOT NULL
  CHECK (status IN (
    'new',
    'paid',
    'shipped',
    'cancelled'
  ))

Проверка нескольких колонок:

CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  starts_at timestamptz NOT NULL,
  ends_at timestamptz NOT NULL,

  CONSTRAINT events_valid_period_check
    CHECK (ends_at > starts_at)
);

PRIMARY KEY

id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY

Первичный ключ сочетает требования UNIQUE и NOT NULL.

FOREIGN KEY

user_id bigint NOT NULL
  REFERENCES users(id)

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

Полная запись:

CONSTRAINT orders_user_id_fkey
  FOREIGN KEY (user_id)
  REFERENCES users(id)
  ON UPDATE RESTRICT
  ON DELETE RESTRICT

Поведение внешнего ключа при удалении

ON DELETE RESTRICT

Запрещает удаление родителя при наличии связанных строк:

user_id bigint NOT NULL
  REFERENCES users(id)
  ON DELETE RESTRICT

Подходит, если удаление родительской записи нарушило бы историю или бизнес-правила.

ON DELETE CASCADE

Автоматически удаляет зависимые строки:

order_id bigint NOT NULL
  REFERENCES orders(id)
  ON DELETE CASCADE

Например, при удалении заказа можно удалить его позиции.

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

ON DELETE SET NULL

Сохраняет дочернюю строку и устанавливает внешний ключ в NULL:

manager_id bigint
  REFERENCES employees(id)
  ON DELETE SET NULL

Колонка не должна иметь ограничение NOT NULL.

ON DELETE SET DEFAULT

Устанавливает значение по умолчанию:

category_id bigint DEFAULT 1
  REFERENCES categories(id)
  ON DELETE SET DEFAULT

Значение по умолчанию должно соответствовать существующей родительской записи.

ON DELETE NO ACTION

Используется по умолчанию. Проверка ограничения может зависеть от того, является ли внешний ключ отложенным.

Для большинства обычных схем выбор делается между RESTRICT, CASCADE и SET NULL.

Нормализация

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

Нормализация помогает:

Нормализация не означает, что любые повторяющиеся значения запрещены. Например, одинаковый статус 'active' у разных пользователей является нормальным повторением значения. Проблемой становится повторное хранение одного и того же факта, который должен изменяться согласованно в нескольких местах.

Аномалии данных

Рассмотрим ненормализованную таблицу:

orders
------------------------------------------------------------------
order_id
order_date
customer_id
customer_name
customer_email
product_id
product_name
product_price
quantity

Один заказ с несколькими товарами занимает несколько строк:

1 | 2026-09-11 | 10 | Анна | a@example.com | 101 | Мышь     | 1500 | 1
1 | 2026-09-11 | 10 | Анна | a@example.com | 102 | Клавиатура | 4000 | 1
2 | 2026-09-12 | 10 | Анна | a@example.com | 101 | Мышь     | 1500 | 2

В таблице повторяются сведения о клиенте, заказе и товаре.

Аномалия обновления

Если пользователь изменил email, его необходимо обновить во всех строках заказов.

Если обновить только часть строк, в базе появятся разные email одного пользователя.

Аномалия вставки

Нельзя добавить товар, пока он не встретился хотя бы в одном заказе, если вся информация хранится только в таблице заказов.

Аномалия удаления

Если удалить последнюю строку с определённым товаром, вместе с историей заказа может исчезнуть единственная информация о товаре.

Нормализация разделяет независимые факты:

customers
products
orders
order_items

Функциональная зависимость

Функциональная зависимость означает, что значение одного набора атрибутов однозначно определяет значение другого набора.

Запись:

A → B

означает: если известно значение A, можно однозначно определить значение B.

Пример:

user_id → email
user_id → name

Для одного user_id существует одно текущее значение email и одно текущее значение name.

Другой пример:

product_id → product_name
product_id → current_price

Если ключ позиции заказа составной:

(order_id, product_id) → quantity

то количество зависит от полной комбинации заказа и товара.

Нормальные формы анализируют зависимости между ключами и неключевыми атрибутами.

Первая нормальная форма — 1NF

Таблица находится в первой нормальной форме, если:

Нарушение: список в одной колонке

CREATE TABLE users (
  id bigint PRIMARY KEY,
  name text NOT NULL,
  phone_numbers text
);

Данные:

1 | Анна | +79990000001, +79990000002

Проблемы:

Нормализованный вариант:

CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE user_phones (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  user_id bigint NOT NULL
    REFERENCES users(id)
    ON DELETE CASCADE,

  phone text NOT NULL,

  CONSTRAINT user_phones_phone_key
    UNIQUE (phone)
);

Если порядок номеров имеет значение:

ALTER TABLE user_phones
ADD COLUMN position integer NOT NULL
CHECK (position > 0);

ALTER TABLE user_phones
ADD CONSTRAINT user_phones_user_position_key
UNIQUE (user_id, position);

Нарушение: повторяющиеся колонки

Неправильно:

CREATE TABLE users (
  id bigint PRIMARY KEY,
  phone_1 text,
  phone_2 text,
  phone_3 text
);

Такая схема:

Правильно создать отдельную таблицу user_phones.

Массивы и JSON

PostgreSQL поддерживает массивы и jsonb, но их использование не всегда нарушает хороший дизайн.

Массив может быть уместен, если:

Отдельная таблица обычно предпочтительнее, если элементы:

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

Вторая нормальная форма — 2NF

Таблица находится во второй нормальной форме, если:

  1. она находится в 1NF;
  2. каждый неключевой атрибут зависит от полного составного ключа, а не от его части.

2NF особенно важна для таблиц с составным первичным ключом.

Рассмотрим таблицу:

order_items
------------------------------------------------
order_id
product_id
order_date
product_name
quantity

Первичный ключ:

(order_id, product_id)

Функциональные зависимости:

order_id → order_date
product_id → product_name
(order_id, product_id) → quantity

order_date зависит только от order_id, а product_name — только от product_id. Это частичные зависимости, нарушающие 2NF.

Нормализованная схема:

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_date date NOT NULL
);

CREATE TABLE products (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE order_items (
  order_id bigint NOT NULL
    REFERENCES orders(id)
    ON DELETE CASCADE,

  product_id bigint NOT NULL
    REFERENCES products(id),

  quantity integer NOT NULL
    CHECK (quantity > 0),

  PRIMARY KEY (order_id, product_id)
);

Теперь:

orders.id → orders.order_date
products.id → products.name
(order_id, product_id) → order_items.quantity

Каждый факт находится в таблице сущности, которую он описывает.

Если первичный ключ состоит из одной колонки, частичная зависимость от части ключа невозможна. Поэтому таблица в 1NF с простым ключом формально автоматически удовлетворяет этому аспекту 2NF.

Третья нормальная форма — 3NF

Таблица находится в третьей нормальной форме, если:

  1. она находится во 2NF;
  2. неключевые атрибуты не зависят транзитивно от ключа через другой неключевой атрибут.

Рассмотрим таблицу сотрудников:

employees
-------------------------------------------------
employee_id
employee_name
department_id
department_name
department_phone

Зависимости:

employee_id → employee_name
employee_id → department_id
department_id → department_name
department_id → department_phone

Следовательно:

employee_id
  → department_id
  → department_name

department_name и department_phone описывают подразделение, а не сотрудника. Это транзитивная зависимость.

Нормализованный вариант:

CREATE TABLE departments (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL UNIQUE,
  phone text
);

CREATE TABLE employees (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,

  department_id bigint NOT NULL
    REFERENCES departments(id)
);

Теперь данные подразделения хранятся один раз.

Изменение телефона подразделения выполняется одной командой:

UPDATE departments
SET phone = '+7-999-000-00-00'
WHERE id = 5;

Нет необходимости обновлять каждого сотрудника отдельно.

Краткое правило 1NF–3NF

Упрощённое правило нормализации:

1NF: одно значение в одной ячейке;
2NF: зависимость от всего ключа;
3NF: зависимость только от ключа.

Более известная формулировка:

Каждый неключевой атрибут должен зависеть
от ключа, от всего ключа и ни от чего,
кроме ключа.

Это полезное практическое правило, хотя формальные определения нормальных форм точнее и учитывают функциональные зависимости и кандидатные ключи.

Нормальная форма Бойса — Кодда

BCNF — Boyce-Codd Normal Form — усиленный вариант 3NF.

Таблица находится в BCNF, если для каждой нетривиальной функциональной зависимости:

X → Y

детерминант X является суперключом.

BCNF важна в схемах с несколькими пересекающимися кандидатными ключами.

Пример таблицы:

teacher_subject_room
--------------------
teacher
subject
room

Предположим:

Возможные зависимости могут привести к ситуации, где таблица удовлетворяет некоторым требованиям 3NF, но всё ещё содержит аномалии. Тогда её разбивают на таблицы, представляющие независимые зависимости.

В большинстве прикладных систем 3NF является разумной базовой целью, а BCNF проверяется для таблиц со сложными кандидатными ключами.

Четвёртая нормальная форма

4NF устраняет независимые многозначные зависимости.

Рассмотрим таблицу:

employee_languages_skills
--------------------------
employee_id
language
skill

Предположим, языки и профессиональные навыки сотрудника не зависят друг от друга.

Данные:

1 | English | SQL
1 | English | Docker
1 | German  | SQL
1 | German  | Docker

Чтобы представить два языка и два навыка, приходится хранить все комбинации.

Правильнее создать две таблицы:

CREATE TABLE employee_languages (
  employee_id bigint NOT NULL
    REFERENCES employees(id)
    ON DELETE CASCADE,

  language_id bigint NOT NULL
    REFERENCES languages(id),

  PRIMARY KEY (employee_id, language_id)
);

CREATE TABLE employee_skills (
  employee_id bigint NOT NULL
    REFERENCES employees(id)
    ON DELETE CASCADE,

  skill_id bigint NOT NULL
    REFERENCES skills(id),

  PRIMARY KEY (employee_id, skill_id)
);

4NF применяется реже, но полезна, когда одна таблица объединяет несколько независимых отношений многие ко многим.

Пошаговая нормализация

Рассмотрим данные заказа:

order_id
order_date
customer_id
customer_name
customer_email
items

В items хранится значение:

101:Мышь:2:1500;102:Клавиатура:1:4000

Шаг 1. Приведение к 1NF

Разделим список товаров на отдельные строки:

order_id
order_date
customer_id
customer_name
customer_email
product_id
product_name
quantity
unit_price

Теперь каждая строка описывает один товар в заказе.

Шаг 2. Приведение к 2NF

Если ключ строки:

(order_id, product_id)

то:

order_date
customer_id

зависят только от order_id.

А:

product_name

зависит только от product_id.

Разделяем данные:

orders
products
order_items

Шаг 3. Приведение к 3NF

В таблице orders могут остаться:

order_id
order_date
customer_id
customer_name
customer_email

Но:

customer_id → customer_name
customer_id → customer_email

Поэтому данные клиента выносятся в отдельную таблицу customers.

Результат:

customers
---------
id
name
email

orders
------
id
customer_id
order_date

products
--------
id
name
current_price

order_items
-----------
order_id
product_id
quantity
unit_price

Почему цена хранится и в товаре, и в позиции заказа

На первый взгляд:

products.current_price
order_items.unit_price

выглядят как дублирование.

Но это разные факты:

Если текущая цена изменится, историческая стоимость заказа должна сохраниться.

CREATE TABLE products (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,

  current_price numeric(12, 2) NOT NULL
    CHECK (current_price >= 0)
);

CREATE TABLE order_items (
  order_id bigint NOT NULL
    REFERENCES orders(id)
    ON DELETE CASCADE,

  product_id bigint NOT NULL
    REFERENCES products(id),

  quantity integer NOT NULL
    CHECK (quantity > 0),

  unit_price numeric(12, 2) NOT NULL
    CHECK (unit_price >= 0),

  PRIMARY KEY (order_id, product_id)
);

Нормализация не запрещает хранить похожие значения, если они отражают разные факты или состояние на разные моменты времени.

Полный пример нормализованной схемы

Пользователи

CREATE TABLE users (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  email text NOT NULL,
  name text NOT NULL,

  status text NOT NULL DEFAULT 'active',

  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT users_email_key
    UNIQUE (email),

  CONSTRAINT users_status_check
    CHECK (status IN (
      'active',
      'blocked',
      'deleted'
    ))
);

Адреса

CREATE TABLE addresses (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  user_id bigint NOT NULL
    REFERENCES users(id)
    ON DELETE CASCADE,

  country_code char(2) NOT NULL,
  city text NOT NULL,
  street text NOT NULL,
  postal_code text,

  is_default boolean NOT NULL DEFAULT false,

  created_at timestamptz NOT NULL DEFAULT now()
);

Если у пользователя может быть только один адрес по умолчанию, обычное ограничение UNIQUE (user_id, is_default) не подходит: оно разрешит только один адрес с false.

В PostgreSQL можно использовать частичный уникальный индекс:

CREATE UNIQUE INDEX addresses_one_default_per_user_idx
ON addresses(user_id)
WHERE is_default;

Категории

CREATE TABLE categories (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  parent_id bigint
    REFERENCES categories(id)
    ON DELETE SET NULL,

  name text NOT NULL,

  CONSTRAINT categories_parent_check
    CHECK (parent_id IS NULL OR parent_id <> id)
);

Это самоссылочная связь:

categories.parent_id → categories.id

Она позволяет создать дерево категорий.

Товары

CREATE TABLE products (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  sku text NOT NULL,
  name text NOT NULL,
  description text,

  current_price numeric(12, 2) NOT NULL,

  status text NOT NULL DEFAULT 'active',

  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT products_sku_key
    UNIQUE (sku),

  CONSTRAINT products_price_check
    CHECK (current_price >= 0),

  CONSTRAINT products_status_check
    CHECK (status IN (
      'draft',
      'active',
      'archived'
    ))
);

Связь товаров и категорий

CREATE TABLE product_categories (
  product_id bigint NOT NULL
    REFERENCES products(id)
    ON DELETE CASCADE,

  category_id bigint NOT NULL
    REFERENCES categories(id)
    ON DELETE CASCADE,

  PRIMARY KEY (product_id, category_id)
);

Заказы

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  user_id bigint NOT NULL
    REFERENCES users(id),

  delivery_address_id bigint
    REFERENCES addresses(id),

  status text NOT NULL DEFAULT 'new',

  created_at timestamptz NOT NULL DEFAULT now(),
  paid_at timestamptz,
  cancelled_at timestamptz,

  CONSTRAINT orders_status_check
    CHECK (status IN (
      'new',
      'paid',
      'processing',
      'shipped',
      'completed',
      'cancelled'
    ))
);

Прямая ссылка на addresses может быть недостаточна для истории: пользователь способен изменить адрес после оформления заказа.

Если адрес заказа должен оставаться неизменным, можно создать отдельную таблицу снимков:

CREATE TABLE order_addresses (
  order_id bigint PRIMARY KEY
    REFERENCES orders(id)
    ON DELETE CASCADE,

  recipient_name text NOT NULL,
  recipient_phone text NOT NULL,
  country_code char(2) NOT NULL,
  city text NOT NULL,
  street text NOT NULL,
  postal_code text
);

Это осознанное хранение исторического состояния, а не случайное дублирование.

Позиции заказа

CREATE TABLE order_items (
  order_id bigint NOT NULL
    REFERENCES orders(id)
    ON DELETE CASCADE,

  product_id bigint NOT NULL
    REFERENCES products(id),

  product_name text NOT NULL,

  quantity integer NOT NULL,
  unit_price numeric(12, 2) NOT NULL,

  PRIMARY KEY (order_id, product_id),

  CONSTRAINT order_items_quantity_check
    CHECK (quantity > 0),

  CONSTRAINT order_items_unit_price_check
    CHECK (unit_price >= 0)
);

product_name здесь может быть сохранён как исторический снимок названия на момент оформления заказа. Если история названий не нужна, его можно получать из products.

Платежи

CREATE TABLE payments (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  order_id bigint NOT NULL
    REFERENCES orders(id),

  external_id text,
  amount numeric(12, 2) NOT NULL,
  status text NOT NULL,

  created_at timestamptz NOT NULL DEFAULT now(),
  completed_at timestamptz,

  CONSTRAINT payments_external_id_key
    UNIQUE (external_id),

  CONSTRAINT payments_amount_check
    CHECK (amount > 0),

  CONSTRAINT payments_status_check
    CHECK (status IN (
      'pending',
      'completed',
      'failed',
      'refunded'
    ))
);

У одного заказа может быть несколько попыток оплаты, поэтому связь имеет вид:

orders 1 ─── N payments

Справочники или CHECK

Статусы можно хранить через CHECK:

status text NOT NULL
CHECK (status IN ('new', 'paid', 'cancelled'))

Преимущества:

Отдельная таблица подходит, если статус имеет дополнительные атрибуты:

CREATE TABLE order_statuses (
  code text PRIMARY KEY,
  display_name text NOT NULL,
  is_final boolean NOT NULL DEFAULT false,
  position integer NOT NULL
);
CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  status_code text NOT NULL
    REFERENCES order_statuses(code)
);

Справочник полезен, если:

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

Имена таблиц и колонок

Для SQL-схемы полезно выбрать единый стиль.

Пример соглашения:

Таблицы:        users, order_items
Первичный ключ: id
Внешний ключ:   user_id, order_id
Дата создания:  created_at
Дата изменения: updated_at
Логическое поле: is_active, has_access

Для PostgreSQL обычно удобно использовать:

Рекомендуется:

CREATE TABLE order_items (
  order_id bigint NOT NULL,
  product_id bigint NOT NULL,
  created_at timestamptz NOT NULL
);

Менее удобно:

CREATE TABLE "OrderItems" (
  "OrderID" bigint NOT NULL,
  "ProdID" bigint NOT NULL
);

Идентификаторы в кавычках становятся чувствительными к регистру и требуют постоянного использования кавычек.

Единственное или множественное число

Допустимы оба варианта:

user
order
product

или:

users
orders
products

Важнее последовательность во всей схеме.

Следует учитывать, что некоторые слова могут совпадать с ключевыми словами SQL. Например, user имеет специальное значение в PostgreSQL, а order связано с ORDER BY.

Поэтому множественные имена часто удобнее:

users
orders
products

Выбор типов данных

Тип должен соответствовать смыслу данных, а не только текущему формату.

Целые числа

quantity integer
id bigint

Денежные значения

price numeric(12, 2)

numeric хранит точные десятичные значения.

Не рекомендуется использовать real или double precision для сумм, где важна точность до копейки.

Альтернативный вариант — хранить сумму в минимальных денежных единицах:

price_minor bigint

Например, 150099 может означать 1500.99. В таком случае единица измерения должна быть явно определена в приложении и документации.

Строки

name text
code varchar(30)

В PostgreSQL text и varchar без ограничения подходят для большинства строковых данных.

varchar(100) следует использовать, если ограничение длины является реальным правилом предметной области:

code varchar(30) NOT NULL

Длину пользовательского имени часто удобнее проверять явно:

name text NOT NULL
CHECK (char_length(name) BETWEEN 1 AND 200)

Дата и время

birth_date date
created_at timestamptz

Для момента создания записи обычно используют:

created_at timestamptz NOT NULL DEFAULT now()

Для даты рождения:

birth_date date

Логические значения

is_active boolean NOT NULL DEFAULT true

Не следует заменять boolean строками:

yes
no
true
false
0
1

если данные действительно имеют два логических состояния.

Перечисления PostgreSQL

CREATE TYPE order_status AS ENUM (
  'new',
  'paid',
  'shipped',
  'cancelled'
);
CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status order_status NOT NULL DEFAULT 'new'
);

ENUM обеспечивает строгий набор значений, но изменение этого набора требует изменения типа. Для часто изменяющихся бизнес-значений отдельный справочник может быть гибче.

NULL и отсутствие значения

NULL не равно пустой строке, нулю или false.

NULL <> ''
NULL <> 0
NULL <> false

Сравнение с NULL выполняется через:

WHERE deleted_at IS NULL

а не:

WHERE deleted_at = NULL

При проектировании следует определить, что означает NULL в каждой колонке.

Например:

middle_name text

NULL может означать, что отчество отсутствует или неизвестно.

Для колонки:

paid_at timestamptz

NULL естественно означает, что заказ ещё не оплачен.

Не следует разрешать NULL без необходимости. Если значение обязательно для корректной записи, нужно использовать NOT NULL.

Производные данные

Производное значение можно вычислить из других данных.

Например:

order_total =
  SUM(order_items.quantity * order_items.unit_price)

Его можно получать запросом:

SELECT
  oi.order_id,
  SUM(oi.quantity * oi.unit_price) AS total
FROM order_items AS oi
WHERE oi.order_id = $1
GROUP BY oi.order_id;

Хранение orders.total создаёт риск рассинхронизации:

orders.total = 5000
сумма позиций = 5500

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

Если итог заказа фиксируется при оформлении, можно хранить его как отдельный бизнес-факт:

total_amount numeric(12, 2) NOT NULL
CHECK (total_amount >= 0)

При этом приложение или процедура должна гарантировать согласованность суммы с позициями на момент фиксации.

Нормализация истории

Текущие данные и исторические данные имеют разные требования.

Например, пользователь изменил имя:

текущее имя: Анна Смирнова
старое имя: Анна Иванова

Если документы должны сохранять имя на момент создания, возможны варианты:

Снимок в документе

CREATE TABLE invoices (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users(id),
  customer_name text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

Таблица версий

CREATE TABLE user_name_history (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  user_id bigint NOT NULL
    REFERENCES users(id),

  name text NOT NULL,
  valid_from timestamptz NOT NULL,
  valid_to timestamptz,

  CONSTRAINT user_name_history_period_check
    CHECK (
      valid_to IS NULL OR valid_to > valid_from
    )
);

Неизменяемый документ

После создания счёт или заказ может сохраняться как неизменяемая запись, даже если справочные данные изменяются.

Такое дублирование не является ошибкой нормализации, если оно отражает самостоятельный исторический факт.

Денормализация

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

Примеры:

Пример:

ALTER TABLE posts
ADD COLUMN comments_count integer NOT NULL DEFAULT 0
CHECK (comments_count >= 0);

При добавлении комментария счётчик должен обновляться согласованно:

BEGIN;

INSERT INTO comments (post_id, author_id, body)
VALUES ($1, $2, $3);

UPDATE posts
SET comments_count = comments_count + 1
WHERE id = $1;

COMMIT;

Денормализация оправдана, когда:

Сначала обычно проектируют нормализованную модель, измеряют запросы, а затем денормализуют конкретные узкие места.

Индексы и проектирование

Нормализация и индексация решают разные задачи:

Индекс внешнего ключа

PostgreSQL не создаёт индекс на внешнем ключе автоматически:

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  user_id bigint NOT NULL
    REFERENCES users(id)
);

Для запросов по пользователю полезен индекс:

CREATE INDEX orders_user_id_idx
ON orders(user_id);

Составной индекс

Для запроса:

SELECT id, created_at
FROM orders
WHERE user_id = $1
  AND status = $2
ORDER BY created_at DESC;

может подойти:

CREATE INDEX orders_user_status_created_idx
ON orders(user_id, status, created_at DESC);

Порядок колонок в составном индексе имеет значение.

Уникальный индекс

Ограничение:

UNIQUE (email)

обычно создаёт уникальный индекс автоматически.

Не нужно создавать второй обычный индекс на ту же колонку без отдельной причины.

Частичный индекс

CREATE INDEX orders_unprocessed_idx
ON orders(created_at)
WHERE status = 'new';

Такой индекс содержит только новые заказы и может быть компактнее полного индекса.

Индекс следует создавать под реальные запросы, а не для каждой колонки.

Уникальность с учётом регистра

Обычное ограничение:

email text NOT NULL UNIQUE

может считать эти значения разными:

User@example.com
user@example.com

Если email должен быть уникален без учёта регистра, можно создать функциональный индекс:

CREATE UNIQUE INDEX users_email_lower_key
ON users(lower(email));

Поиск должен использовать соответствующее выражение:

SELECT id, email
FROM users
WHERE lower(email) = lower($1);

Другой вариант — нормализовать email перед сохранением и хранить его в выбранном регистре.

Для некоторых задач можно использовать тип citext, но правила сравнения и локали всё равно следует выбирать осознанно.

Мягкое удаление

При мягком удалении строка сохраняется, но получает отметку:

ALTER TABLE users
ADD COLUMN deleted_at timestamptz;

Активные пользователи:

SELECT id, email
FROM users
WHERE deleted_at IS NULL;

Если email должен быть уникален только среди активных пользователей:

CREATE UNIQUE INDEX users_active_email_key
ON users(lower(email))
WHERE deleted_at IS NULL;

Мягкое удаление усложняет схему:

Его следует использовать, когда действительно нужны восстановление, аудит или юридически значимая история.

Полиморфные связи

Иногда пытаются создать универсальную таблицу:

CREATE TABLE comments (
  id bigint PRIMARY KEY,
  target_type text NOT NULL,
  target_id bigint NOT NULL,
  body text NOT NULL
);

Записи:

target_type = 'post', target_id = 10
target_type = 'photo', target_id = 25

PostgreSQL не может создать обычный внешний ключ target_id сразу на несколько таблиц. Поэтому база не гарантирует существование целевого объекта.

Более строгий вариант — отдельные таблицы связей:

CREATE TABLE post_comments (
  comment_id bigint PRIMARY KEY
    REFERENCES comments(id)
    ON DELETE CASCADE,

  post_id bigint NOT NULL
    REFERENCES posts(id)
    ON DELETE CASCADE
);
CREATE TABLE photo_comments (
  comment_id bigint PRIMARY KEY
    REFERENCES comments(id)
    ON DELETE CASCADE,

  photo_id bigint NOT NULL
    REFERENCES photos(id)
    ON DELETE CASCADE
);

Другой подход — общая родительская сущность:

CREATE TABLE content_objects (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  object_type text NOT NULL
);

Тогда комментарий может ссылаться на content_objects(id) обычным внешним ключом.

Полиморфная связь через type + id удобна в коде, но ослабляет ссылочную целостность.

Наследование сущностей

Рассмотрим клиентов двух типов:

Одна таблица

CREATE TABLE customers (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  customer_type text NOT NULL,

  first_name text,
  last_name text,

  company_name text,
  tax_number text,

  CONSTRAINT customers_type_check
    CHECK (customer_type IN (
      'person',
      'company'
    ))
);

Преимущество — простые запросы.

Недостатки:

Можно добавить проверку:

CONSTRAINT customers_fields_check
CHECK (
  (
    customer_type = 'person'
    AND first_name IS NOT NULL
    AND last_name IS NOT NULL
    AND company_name IS NULL
    AND tax_number IS NULL
  )
  OR
  (
    customer_type = 'company'
    AND first_name IS NULL
    AND last_name IS NULL
    AND company_name IS NOT NULL
    AND tax_number IS NOT NULL
  )
)

Общая и специализированные таблицы

CREATE TABLE customers (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE
);
CREATE TABLE person_customers (
  customer_id bigint PRIMARY KEY
    REFERENCES customers(id)
    ON DELETE CASCADE,

  first_name text NOT NULL,
  last_name text NOT NULL
);
CREATE TABLE company_customers (
  customer_id bigint PRIMARY KEY
    REFERENCES customers(id)
    ON DELETE CASCADE,

  company_name text NOT NULL,
  tax_number text NOT NULL UNIQUE
);

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

Выбор зависит от количества общих и специализированных полей, частоты запросов и сложности правил.

Локализация данных

Не рекомендуется создавать отдельные колонки для заранее неизвестного количества языков:

name_ru text,
name_en text,
name_de text

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

Нормализованный вариант:

CREATE TABLE products (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku text NOT NULL UNIQUE
);
CREATE TABLE product_translations (
  product_id bigint NOT NULL
    REFERENCES products(id)
    ON DELETE CASCADE,

  language_code varchar(10) NOT NULL,
  name text NOT NULL,
  description text,

  PRIMARY KEY (product_id, language_code)
);

Теперь новый язык добавляется строками, а не колонками.

Аудит изменений

Поля:

created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()

хранят только время последнего изменения, но не историю.

Если необходимо знать, кто и что изменил, используют журнал:

CREATE TABLE order_status_history (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,

  order_id bigint NOT NULL
    REFERENCES orders(id),

  old_status text,
  new_status text NOT NULL,

  changed_by bigint
    REFERENCES users(id),

  changed_at timestamptz NOT NULL DEFAULT now(),

  reason text
);

История статусов представляет самостоятельную сущность и может использоваться для аудита и аналитики.

Представления

Представление — VIEW сохраняет запрос как виртуальную таблицу.

CREATE VIEW order_totals AS
SELECT
  oi.order_id,
  SUM(oi.quantity * oi.unit_price) AS total_amount
FROM order_items AS oi
GROUP BY oi.order_id;

Использование:

SELECT
  o.id,
  o.created_at,
  ot.total_amount
FROM orders AS o
JOIN order_totals AS ot
  ON ot.order_id = o.id;

Представление:

Материализованное представление

CREATE MATERIALIZED VIEW daily_sales AS
SELECT
  created_at::date AS sale_date,
  SUM(total_amount) AS total_amount
FROM orders
WHERE status = 'completed'
GROUP BY created_at::date;

Материализованное представление физически хранит результат.

Обновление:

REFRESH MATERIALIZED VIEW daily_sales;

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

Разделение схем PostgreSQL

В одной базе PostgreSQL можно использовать несколько схем:

CREATE SCHEMA app;
CREATE SCHEMA audit;
CREATE SCHEMA reporting;

Таблицы:

CREATE TABLE app.users (...);
CREATE TABLE audit.events (...);
CREATE TABLE reporting.daily_sales (...);

Схемы помогают:

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

Процесс проектирования

Шаг 1. Собрать требования

Нужно описать:

Шаг 2. Найти сущности

Существительные в требованиях часто становятся кандидатами на сущности:

пользователь
заказ
товар
категория
платёж

Не каждое существительное требует отдельной таблицы. Например, «цвет кнопки» может быть атрибутом настройки, а не самостоятельной сущностью.

Шаг 3. Определить атрибуты

Для каждого атрибута нужно определить:

Шаг 4. Определить ключи

Для каждой сущности выбираются:

Шаг 5. Определить связи

Для каждой связи устанавливаются:

Шаг 6. Проверить нормальные формы

Следует найти:

Шаг 7. Добавить ограничения

Правила предметной области закрепляются через:

PRIMARY KEY
FOREIGN KEY
UNIQUE
NOT NULL
CHECK

Шаг 8. Проанализировать запросы

Для основных операций необходимо проверить:

Шаг 9. Проверить конкурентный доступ

Следует определить:

Шаг 10. Создать миграции

Схема должна изменяться версионированными миграциями, а не ручными командами в рабочей базе.

Типичные ошибки проектирования

Несколько сущностей в одной таблице

order_id
customer_name
customer_email
product_name
product_price

Такая таблица смешивает заказ, клиента и товар.

Списки идентификаторов в строке

category_ids = "1,5,12"

Следует использовать промежуточную таблицу.

Универсальная таблица атрибутов

entity_id
attribute_name
attribute_value

Модель EAV кажется гибкой, но:

EAV может применяться для действительно динамических свойств, но не должна заменять обычные колонки для основных данных.

Избыточное использование JSON

CREATE TABLE orders (
  id bigint PRIMARY KEY,
  data jsonb NOT NULL
);

Если внутри JSON находятся стабильные поля заказа, связи и суммы, база не сможет полноценно обеспечивать их целостность.

JSON подходит для:

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

Отсутствие ограничений

Проверка только в приложении не защищает от:

Использование имени как ключа

orders.customer_name

Имя не является надёжным идентификатором: оно может повторяться и изменяться.

Нужна ссылка:

orders.user_id

Хранение возраста

Возраст изменяется со временем:

age integer

Лучше хранить дату рождения:

birth_date date

Возраст вычисляется на нужную дату.

Хранение текущего состояния без истории

Если нужно анализировать переходы статусов, одного поля недостаточно:

orders.status

Потребуется таблица истории.

Создание индекса для каждой колонки

Каждый индекс:

Индексы проектируются под реальные запросы и ограничения.

Отсутствие индексов внешних ключей

Внешний ключ поддерживает целостность, но не гарантирует быстрый поиск дочерних строк.

Чрезмерная денормализация

Большое количество копий одних данных создаёт сложную систему синхронизации и повышает риск противоречий.

Чрезмерная нормализация

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

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

Проверка схемы

Для каждой таблицы полезно задать вопросы:

  1. Что представляет одна строка?
  2. Как строка уникально идентифицируется?
  3. Все ли колонки описывают именно эту сущность?
  4. Есть ли списки или повторяющиеся группы?
  5. Зависит ли каждый атрибут от полного ключа?
  6. Нет ли зависимости от другого неключевого атрибута?
  7. Какие значения могут быть NULL и почему?
  8. Какие ограничения должна проверять база?
  9. Что происходит при удалении связанных строк?
  10. Нужна ли история изменений?
  11. Какие запросы выполняются чаще всего?
  12. Какие индексы поддерживают эти запросы?
  13. Можно ли восстановить согласованность денормализованных данных?
  14. Какие операции должны выполняться в транзакции?

Краткий чек-лист нормализации

Схема близка к 1NF, если:

Схема близка к 2NF, если:

Схема близка к 3NF, если:

Итоговый пример запроса

Получение заказов с пользователем, позициями и итоговой суммой:

SELECT
  o.id AS order_id,
  o.created_at,
  o.status,

  u.id AS user_id,
  u.email,

  COUNT(oi.product_id) AS item_count,

  SUM(
    oi.quantity * oi.unit_price
  ) AS total_amount

FROM orders AS o

JOIN users AS u
  ON u.id = o.user_id

JOIN order_items AS oi
  ON oi.order_id = o.id

WHERE o.created_at >= $1
  AND o.created_at < $2

GROUP BY
  o.id,
  o.created_at,
  o.status,
  u.id,
  u.email

ORDER BY o.created_at DESC;

Соответствующие индексы:

CREATE INDEX orders_created_at_idx
ON orders(created_at);

CREATE INDEX orders_user_id_idx
ON orders(user_id);

CREATE INDEX order_items_product_id_idx
ON order_items(product_id);

Первичный ключ:

PRIMARY KEY (order_id, product_id)

уже создаёт индекс, начинающийся с order_id, поэтому дополнительный индекс только на order_items(order_id) обычно не требуется.

Итог

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

Нормализация помогает разместить каждый факт в подходящей таблице:

1NF — одно значение в одной ячейке;
2NF — зависимость от полного ключа;
3NF — отсутствие зависимостей между неключевыми атрибутами.

Хорошая практическая схема обычно стремится к 3NF, но учитывает историю, производительность и реальные сценарии работы. Денормализация допустима, если она выполняется осознанно, измеряется и сопровождается надёжным механизмом синхронизации.

Главный принцип проектирования можно сформулировать так:

База данных должна не только хранить корректные данные, но и по возможности не позволять сохранить некорректные.