Как спроектировать хорошую схему базы данных для PHP и Yii
Схема базы данных задаёт правила, которые действуют и для Yii Active Record, и для фоновых задач, и для прямых SQL-запросов. Разберём на примере заказов, как выбрать ключи и типы, выразить связи в MySQL и менять схему в работающем приложении.
1. Начните с правил предметной области
Перед созданием таблиц запишите, что означает одна строка и какие состояния допустимы. Например, каждый заказ принадлежит одному покупателю, у покупателя может быть много заказов, а номер заказа уникален в пределах магазина. Уточните, можно ли менять владельца заказа и что должно происходить с историей покупок при закрытии аккаунта. Эти ответы определяют, где нужны NOT NULL, внешний ключ и составной UNIQUE.
Храните покупателя и заказ в отдельных таблицах. Для связи «многие ко многим» заведите таблицу связей с двумя внешними ключами и уникальной парой. Если в связи появляются собственные данные, например количество и цена товара в заказе, дайте ей понятное имя вроде order_item. Цена на момент покупки принадлежит позиции заказа: изменение цены в каталоге не должно менять старый чек.
2. Договоритесь об именах таблиц, полей и связей
В новом проекте удобно выбрать snake_case и имена таблиц в единственном числе: customer, sales_order, order_item. Тогда классы Customer, SalesOrder и OrderItem легко сопоставить со схемой. Если проект уже использует множественное число, придерживайтесь принятого соглашения и задайте имя таблицы через tableName(). Префикс общего подключения можно выразить в Yii 2 через {{%sales_order}}, оставив имя класса SalesOrder.
Внутри таблицы достаточно имён id, status, created_at. У внешних ключей указывайте роль: customer_id, billing_address_id, shipping_address_id. В запросах с соединениями уточняйте одинаковые имена через псевдонимы таблиц. В модели оставляйте customer_id для атрибута, а customer для отношения. Имя orders подсказывает, что при чтении свойства Yii вернёт коллекцию в виде массива моделей, в том числе пустого.
3. Выберите первичные ключи и типы данных
Для самостоятельной сущности обычно подходит неизменяемый id с AUTO_INCREMENT. Так номер заказа можно хранить под отдельным ограничением уникальности и менять по правилам бизнеса, сохраняя ссылки из других таблиц. Составной первичный ключ уместен в таблице связей, если пара идентификаторов полностью определяет строку. Например, пара (order_id, product_id) подходит, только если один товар может встречаться в заказе максимум один раз.
Выбирайте размер целого по ожидаемому диапазону и используйте одинаковый тип и знаковость в первичном и внешнем ключах. Учитывайте границу между MySQL и PHP: верхняя часть диапазона BIGINT UNSIGNED выходит за пределы int даже в 64-битном PHP. Если схема допускает такие значения, передавайте их как десятичные строки и исключите преобразование через float.
- Для денег используйте целые суммы в минимальных единицах валюты или
DECIMALс масштабом, который подходит предметной области. Храните валюту отдельно, если их несколько. Десятичные суммы, полученные строкой из драйвера, сохраняйте точными и в PHP. - Для календарной даты берите
DATE. Для момента события можно выбратьDATETIMEи договориться записывать UTC: сама колонка часовой пояс не хранит. Переводите время в пользовательский пояс при выводе. - Для текста задайте
utf8mb4и осознанно выберите правила сравнения. От них зависит, сочтёт ли уникальный индекс два значения, отличающиеся регистром или акцентом, одинаковыми. Если поле обязательно и уникально, задайте одновременноNOT NULLиUNIQUE: MySQL допускает несколькоNULLв уникальном индексе.
4. Закрепите связи и ограничения в MySQL
Внешний ключ InnoDB защищает целостность при любом способе записи. Валидация в Yii помогает показать понятную ошибку, а UNIQUE в базе разрешает гонку, когда два запроса одновременно пытаются записать один номер. В примере номер уникален во всём приложении. Для нескольких магазинов с независимой нумерацией добавьте store_id и перенесите уникальность на пару (store_id, order_number).
CREATE TABLE customer (
id BIGINT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE sales_order (
id BIGINT NOT NULL AUTO_INCREMENT,
customer_id BIGINT NOT NULL,
order_number VARCHAR(32) NOT NULL,
total_minor BIGINT NOT NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uq_sales_order_number (order_number),
KEY ix_sales_order_customer_created (customer_id, created_at, id),
CHECK (total_minor >= 0),
-- Сохраняем историю заказов при закрытии аккаунта.
CONSTRAINT fk_sales_order_customer FOREIGN KEY (customer_id)
REFERENCES customer (id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;RESTRICT отклоняет удаление покупателя, пока есть заказы. CASCADE подходит для зависимых строк, которые должны исчезнуть вместе с родителем. SET NULL подходит, когда строка сохраняет смысл без связи и колонка допускает NULL. Для отношения «один к одному» добавьте UNIQUE к внешнему ключу. Например, уникальный customer_id в таблице профилей гарантирует максимум один профиль на покупателя.
5. Отразите схему в моделях Yii 2
На PHP 8.3–8.5 задавайте типы аргументов и результатов в собственных методах и используйте declare(strict_types=1) в PHP-файлах приложения. В Yii 2 атрибуты Active Record доступны динамически. Объявление публичного типизированного свойства с именем колонки может перекрыть доступ Active Record к этому атрибуту. Для строгих типов на границе приложения удобно преобразовывать данные в отдельный типизированный объект.
<?php
declare(strict_types=1);
namespace app\models;
use yii\db\ActiveQuery;
use yii\db\ActiveRecord;
final class SalesOrder extends ActiveRecord
{
public static function tableName(): string
{
return 'sales_order';
}
public function getCustomer(): ActiveQuery
{
// Ключ покупателя сопоставляем с внешним ключом заказа.
return $this->hasOne(Customer::class, ['id' => 'customer_id']);
}
}Пример предполагает, что модель Customer из того же пространства имён сопоставлена с таблицей customer. Метод getCustomer() возвращает объект запроса. Свойство $order->customer возвращает связанную модель или null, если запрос её не нашёл. Для обратной связи объявите getOrders(): ActiveQuery и верните $this->hasMany(SalesOrder::class, ['customer_id' => 'id']). Если на странице списка выводятся данные покупателей, добавьте with('customer') к запросу заказов, чтобы избежать отдельного запроса для каждой строки.
6. Подбирайте индексы под реальные запросы
Начните со сценариев чтения: найти заказ по номеру, вывести последние заказы покупателя, выгрузить заказы за период. У каждого сценария свои фильтры и порядок сортировки. Индекс (customer_id, created_at, id) подходит для списка заказов конкретного покупателя. id задаёт однозначный порядок, когда у нескольких заказов одинаковый created_at.
-- Последние 20 заказов одного покупателя.
SELECT id, order_number, total_minor, created_at
FROM sales_order
WHERE customer_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20;Составной индекс начинается с customer_id, поэтому он также подходит для поиска по одному этому полю и поддерживает внешний ключ. Для отчёта по датам сразу по всем покупателям проверьте план отдельно: дата здесь находится после идентификатора покупателя. Смотрите EXPLAIN на данных с реалистичным распределением и добавляйте новый индекс, когда запросу нужен другой порядок колонок. Каждый дополнительный индекс занимает место и обслуживается при записи.
7. Меняйте схему поэтапно
Храните миграции рядом с кодом приложения. На пустой базе проверяйте, что они создают всю схему, а на копии рабочих данных проверяйте дубли, NULL, потерянные ссылки и время выполнения. Заранее выясните, как конкретный ALTER TABLE повлияет на блокировки и потребуется ли перестроение таблицы. Операции DDL в MySQL могут неявно фиксировать транзакцию, поэтому safeUp() в Yii 2 сам по себе не гарантирует откат всей миграции.
Если нужно сделать существующее поле обязательным, сначала разверните код, который всегда записывает значение, и убедитесь, что старые процессы завершились. Затем заполните пропуски небольшими порциями, повторно проверьте данные и добавьте NOT NULL. При замене колонки сначала добавьте новую, обеспечьте совместимость чтения и записи во время выкладки, перенесите данные и переключите чтение. Удаляйте старую колонку после того, как её перестали использовать все процессы, включая фоновые задачи.
8. Чек-лист перед выпуском
- Для каждой таблицы понятно, что означает строка и какие поля могут отсутствовать.
- Первичный ключ стабилен, бизнес-уникальность выражена отдельным ограничением, а типы внешних ключей совпадают с типами родительских.
- Правила удаления проверены на связанных данных, включая историю покупок.
- Имена колонок и отношений различаются, а по названию связи понятно, ожидается одна модель или массив.
- Типы сумм, дат и идентификаторов согласованы с PHP и настройками подключения.
- Для основных запросов проверены планы выполнения, а миграции испытаны на пустой схеме и на копии рабочих данных.
Частые вопросы
Обязательно ли называть таблицы в единственном числе в Yii?
Yii 2 позволяет явно указать имя таблицы через tableName(). Выберите соглашение для всего проекта, а имена классов моделей давайте в единственном числе, поскольку экземпляр модели представляет одну строку.
Когда в MySQL нужен составной первичный ключ?
Когда комбинация колонок однозначно определяет строку и остаётся стабильной. Типичный пример — таблица связи пользователей и ролей, где каждая пара может встретиться один раз. Если у записи есть собственный жизненный цикл и на неё ссылаются другие таблицы, рассмотрите отдельный id и уникальное ограничение на пару.
Достаточно ли связей Active Record для целостности данных?
Связи Active Record описывают, как Yii загружает модели. Целостность данных при записи обеспечивают ограничения MySQL: внешний ключ проверяет существование родительской строки, NOT NULL делает связь обязательной, а UNIQUE задаёт допустимую кратность.