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.

Файл з рішенням.