Коротко и по делу.
1) Нормальные формы текущих таблиц
- Users(id, name)
- Ключ: $\text{PK}=\{id\}$. Функциональная зависимость $id\to name$.
- НФ: удовлетворяет 1NF, 2NF, 3NF и BCNF (нет нетривиальных зависимостей с частью ключа или транзитивных зависимостей).
- Orders(id, user_id, total)
- Ключ: $\text{PK}=\{id\}$. FDs: $id\to user\_id,total$.
- НФ: 1NF/2NF/3NF/BCNF по формальным правилам (нет составного ключа и нет транзитивных зависимостей).
- Замечание: атрибут $total$ логически связан с платежами (он может быть производной/согласованной величиной), поэтому присутствие $total$ создаёт избыточность и риск аномалий (см. ниже).
- Payments(id, order_id, amount)
- Ключ: $\text{PK}=\{id\}$. FDs: $id\to order\_id,amount$.
- НФ: 1NF/2NF/3NF/BCNF формально удовлетворяются.
- Замечание: если Payments хранит только положительные фактические списания, а возвраты не отражаются, то семантически таблица неполная.
2) Возможные аномалии (примеры)
- Insert:
- Невозможно вставить платёж без существующего заказа (FK) — нормально, но если нужен учёт предавторизаций/инициаций, текущая схема не поддерживает.
- Невозможно зарегистрировать возврат без отдельной структуры — придётся менять Payments (анти-платёж) или удалять/редактировать записи -> ошибки.
- Update:
- Если $total$ в Orders не согласован с суммой платежей в Payments, возникает аномалия обновления: нужно менять и тот, и другое -> возможная рассинхронизация.
- Обновление статуса/баланса заказа требует атомарного изменения в нескольких таблицах.
- Delete:
- Удаление заказа может потерять историю платежей (если каскадно удалить). Удаление платежа стирает историю операций — нарушение требований учёта.
3) Предложение переработанной схемы для частичных платежей и истории возвратов
Рекомендуемый набор таблиц (ключи и целостность):
- Users
- columns: $id(\text{PK}),name,\dots$
- Orders
- columns: $id(\text{PK}),user\_id(\text{FK Users}),total\_amount,status,created\_at,updated\_at$ - constraint: $total\_amount\ge 0$
- OrderItems (опционально, для правильного расчёта total)
- columns: $id(\text{PK}),order\_id(\text{FK Orders}),product\_id,qty,unit\_price$ - total\_amount вычисляется как $\sum qty\times unit\_price$ (в приложение/отдельном процессе)
- Payments (реестр платёжных транзакций, допускает частичные платежи)
- columns: $id(\text{PK}),order\_id(\text{FK Orders}),amount,method,status,paid\_at,external\_ref,idempotency\_key$ - constraints: $amount>0$ - каждая запись — отдельная операция оплаты (частичный платёж возможен как отдельная запись)
- Refunds (история возвратов)
- columns: $id(\text{PK}),payment\_id(\text{FK Payments},\text{nullable}),order\_id(\text{FK Orders}),amount,reason,refunded\_at,status$ - constraints: $amount>0$ - связь с payment_id позволяет отслеживать возврат конкретной транзакции; nullable для случаев off-ledger refunds
- (Опционально) PaymentAllocations — если платёж может распределяться по нескольким заказам/позициям
- columns: $id,payment\_id,order\_id,amount$
Баланс/остаток вычислять динамически (рекомендуется не хранить как исходную авторитетную колонку, либо иметь колонку cached_balance с механизмом обновления в транзакции):
- Формула остатка: $\text{outstanding}=\text{total\_amount}-\sum \text{payments.amount}+\sum \text{refunds.amount}$
4) Как это повлияет на транзакционность и рекомендации
- Атомарность операций: вставка записи в Payments и одновременное изменение кэша баланса/статуса заказа (например, перевод в PAID) должна выполняться в одной транзакции ACID, чтобы избежать рассинхронизации. Аналогично для Refunds.
- Конкуренция: при частичных платежах возможны гонки (две параллельные попытки оплатить остаток). Решения:
- использовать явную блокировку строки заказа: $\text{SELECT ... FOR UPDATE}$, или
- optimistic locking (версия/etag) + повторные попытки, или
- повышенная изоляция (SERIALIZABLE) в критичных сценариях.
- Идемпотентность: для внешних платёжных шлюзов хранить $idempotency\_key$ и внешние референсы, чтобы избежать дублирующих записей при повторных callback'ах.
- Среда согласованности: лучше не хранить производные данные (например, cached_balance) без строгой транзакционной логики или фоновой репликации + проверки. Если храните cached_balance, обновляйте его внутри той же транзакции, где вставлен Payments/Refunds, либо используйте транзакционные триггеры/handler'ы.
- Журнал операций: Payments и Refunds выступают как неизменяемый журнал (append-only) — обеспечивает историю и audit trail. Не удаляйте записи, помечайте статусом.
Короткое резюме:
- Текущие таблицы формально в 3NF/BCNF, но схема содержит семантическую избыточность ($Orders.total$), что приводит к аномалиям.
- Для частичных платежей и возвратов — использовать отдельные таблицы Payments и Refunds (ledger-подход), хранить каждую операцию отдельно, вычислять баланс как агрегат.
- Обеспечить согласованность операциями в пределах транзакций, использовать блокировки/версии и идемпотентные ключи для внешних интеграций.