You’ve got a database, you’ve got tables. You even filled those tables with data. Now it’s time to make sure your data stays consistent.
When you created tables inside each schema, foreign keys to tables in the same schema were added automatically. Now you need to link tables from different schemas together — add foreign keys.
For example, the order table from the order schema references a user from the account table in the user schema. You need to add a foreign key for this table. The query might look like this:
ALTER TABLE "order".order
ADD CONSTRAINT fk_order_user
FOREIGN KEY (user_id)
REFERENCES "user".account(id)
ON DELETE RESTRICT;List of Foreign Keys
You’ll need to add 40 foreign keys in total :)
Schema product:
[product].review.user_id → [user].account.id
Product reviews should be linked to the users who left them.
[product].question.user_id → [user].account.id
Only registered users can ask questions about products.
[product].answer.user_id → [user].account.id
Answers also belong to users.
Schema order:
[order].order.user_id → [user].account.id
The user who placed the order is always a registered user.
[order].order_item.product_id → [product].product.id
An order item always points to a specific product.
[order].order_item.variant_id → [product].variant.id
If a product variant is chosen, it must exist. But the variant can be deleted (then the field becomes NULL).
[order].cart.user_id → [user].account.id
The cart belongs to a specific user.
[order].cart_item.product_id → [product].product.id
The product in the cart must be a valid product.
[order].cart_item.variant_id → [product].variant.id
The chosen product variant must exist if specified. If deleted — the field becomes NULL.
Schema logistics:
[logistics].inventory.variant_id → [product].variant.id
The variant can be deleted (like, taken off sale).
[logistics].inventory_movement.product_id → [product].product.id
Any warehouse movement is about a real product.
[logistics].inventory_movement.variant_id → [product].variant.id
The movement can be for a specific product variant.
[logistics].stock_level.product_id → [product].product.id
Stock levels are set for existing products.
[logistics].transfer.product_id → [product].product.id
Transfers only happen for real products.
[logistics].transfer.variant_id → [product].variant.id
The variant can be deleted.
[logistics].package.order_id → [order].order.id
Each package is linked to a specific order.
Schema payment:
[payment].payment_transaction.order_id → [order].order.id
A payment transaction always belongs to an order.
[payment].invoice.order_id → [order].order.id The invoice is issued for an order.
[payment].billing_address.user_id → [user].account.id
The billing address belongs to a user.
[payment].wallet.user_id → [user].account.id
The wallet is unique for each user.
Schema marketing:
[marketing].discount.product_id → [product].product.id
A discount can be tied to a specific product.
[marketing].discount.category_id → [product].category.id
A discount can be tied to a product category.
[marketing].discount.brand_id → [product].brand.id
Brand discount.
[marketing].promo_usage.user_id → [user].account.id
Only registered users can use promo codes.
[marketing].featured_product.product_id → [product].product.id
The featured product on the main page must exist.
[marketing].referral_use.referrer_id → [user].account.id
The inviter is a required user.
[marketing].referral_use.referee_id → [user].account.id
The invited person is a required user.
Schema support:
[support].support_ticket.user_id → [user].account.id
Every ticket is created by a user.
[support].support_ticket.category_id → [support].ticket_category.id
The ticket category can be deleted, but the ticket stays.
[support].ticket_message.sender_id → [user].account.id
Messages are sent by users (or agents, if they’re also in user.account).
[support].support_agent.user_id → [user].account.id
A support agent is always a user.
[support].ticket_status_log.changed_by → [user].account.id
We log who changed the status (user or staff).
Schema content:
[content].article.author_id → [user].account.id
The article is written by a registered user.
[content].media.uploaded_by → [user].account.id
Who uploaded the media file (can be deleted).
[content].content_revision.author_id → [user].account.id
Who made the change (can be deleted).
Note
Wherever possible, you should use ON DELETE SET NULL, so when an entity is deleted, info about the links isn’t lost completely.
For linking entities using the "target_type/target_id" pattern (like user.comment), you can’t use a foreign key at the SQL level — those links have to be handled in your app code.
This list covers the main cross-schema relationships for basic data integrity at the SQL level.
File with the solution.