Foreign Keys для связывания схем Marketplace

У вас есть база данных, у вас есть таблицы. И вы даже заполнили эти таблицы данными. Теперь настало время позаботиться об целостности этих данных.

При создании таблиц внутри каждой схемы были автоматически добавлены внешний ключи (foreign keys) на таблицы этой же схемы. Теперь вам будет нужно связать между собой таблицы разных схем - добавить внешние ключи.

Например таблица order из схемы order ссылается на пользователя из таблицы account схемы user. Вам нужно добавить внешний ключ для этой таблицы. Выглядеть такой запрос может примерно так:

ALTER TABLE "order".order
  ADD CONSTRAINT fk_order_user
  FOREIGN KEY (user_id)
  REFERENCES "user".account(id)
  ON DELETE RESTRICT;

Список внешних ключей

Всего нужно будет добавить 40 внешних ключей :)

Схема product:

[product].review.user_id → [user].account.id

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

[product].question.user_id → [user].account.id

Вопросы о товаре задают зарегистрированные пользователи.

[product].answer.user_id → [user].account.id

Ответы также принадлежат пользователям.

Схема order:

[order].order.user_id → [user].account.id

Пользователь, оформивший заказ — всегда зарегистрированный пользователь.

[order].order_item.product_id → [product].product.id

Позиция заказа всегда ссылается на конкретный товар.

[order].order_item.variant_id → [product].variant.id

Если выбран вариант товара — он должен существовать. Но вариант может быть удалён (тогда поле станет NULL).

[order].cart.user_id → [user].account.id

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

[order].cart_item.product_id → [product].product.id

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

[order].cart_item.variant_id → [product].variant.id

Выбранный вариант товара должен существовать, если указан. Если удалён — поле становится NULL.

Схема logistics:

[logistics].inventory.variant_id → [product].variant.id

Вариант может быть удалён (например, снят с продажи).

[logistics].inventory_movement.product_id → [product].product.id

Любое движение на складе относится к реальному товару.

[logistics].inventory_movement.variant_id → [product].variant.id

Вид движения может быть по конкретному варианту товара.

[logistics].stock_level.product_id → [product].product.id

Уровень запасов устанавливается по существующим товарам.

[logistics].transfer.product_id → [product].product.id

Перемещения только по реально существующему товару.

[logistics].transfer.variant_id → [product].variant.id

Вариант может быть удалён.

[logistics].package.order_id → [order].order.id

Каждая упаковка связана с конкретным заказом.

Схема payment:

[payment].payment_transaction.order_id → [order].order.id

Транзакция по оплате всегда относится к заказу.

[payment].invoice.order_id → [order].order.id Счёт выставляется по заказу.

[payment].billing_address.user_id → [user].account.id

Адрес выставления счета принадлежит пользователю.

[payment].wallet.user_id → [user].account.id

Кошелёк уникален для пользователя.

Схема marketing:

[marketing].discount.product_id → [product].product.id

Скидка может быть привязана к определённому товару.

[marketing].discount.category_id → [product].category.id

Скидка может быть привязана к категории товаров.

[marketing].discount.brand_id → [product].brand.id

Скидка для бренда.

[marketing].promo_usage.user_id → [user].account.id

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

[marketing].featured_product.product_id → [product].product.id

Избранный товар на витрине должен существовать.

[marketing].referral_use.referrer_id → [user].account.id

Кто пригласил — обязательный пользователь.

[marketing].referral_use.referee_id → [user].account.id

Кого пригласили — обязательный пользователь.

Схема support:

[support].support_ticket.user_id → [user].account.id

Каждая заявка создаётся пользователем.

[support].support_ticket.category_id → [support].ticket_category.id

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

[support].ticket_message.sender_id → [user].account.id

Сообщения отправляют пользователи (или агенты, если они тоже в user.account).

[support].support_agent.user_id → [user].account.id

Агент поддержки — это всегда пользователь.

[support].ticket_status_log.changed_by → [user].account.id

Фиксируем, кто сменил статус (пользователь или сотрудник).

Схема content:

[content].article.author_id → [user].account.id

Статья написана зарегистрированным пользователем.

[content].media.uploaded_by → [user].account.id

Кто загрузил медиафайл (может быть удалён).

[content].content_revision.author_id → [user].account.id

Кто сделал изменение (может быть удалён).

Примечание

Везде, где возможно, стоит предусмотреть ON DELETE SET NULL, чтобы при удалении сущности информация о связях не терялась полностью.

Для связывания сущностей через тип "target_type/target_id" (например, user.comment), внешний ключ невозможен на уровне SQL, такие связи должны контролироваться на уровне приложения.

Этот список охватывает основные кросс-схемные связи для базовой целостности данных на уровне SQL.

Файл с решением.