Foreign Keys pour relier les schémas Marketplace

Tu as une base de données, tu as des tables. Et t'as même déjà rempli ces tables avec des données. Maintenant, c'est le moment de te soucier de l'intégrité de ces données.

Quand tu crées des tables dans chaque schéma, les foreign keys vers les tables du même schéma sont ajoutées automatiquement. Maintenant, tu dois relier les tables de différents schémas entre elles – ajouter des foreign keys.

Par exemple, la table order du schéma order fait référence à un utilisateur dans la table account du schéma user. Tu dois ajouter une foreign key pour cette table. Une requête pourrait ressembler à ça :

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

Liste des foreign keys

Au total, tu vas devoir ajouter 40 foreign keys :)

Schéma product :

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

Les avis sur les produits doivent être liés aux utilisateurs qui les ont laissés.

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

Les questions sur un produit sont posées par des utilisateurs enregistrés.

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

Les réponses appartiennent aussi à des utilisateurs.

Schéma order :

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

L'utilisateur qui passe la commande est toujours un utilisateur enregistré.

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

Un item de commande fait toujours référence à un produit précis.

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

Si une variante de produit est choisie, elle doit exister. Mais la variante peut être supprimée (dans ce cas, le champ devient NULL).

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

Le panier appartient à un utilisateur précis.

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

Le produit dans le panier doit être un produit valide.

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

La variante choisie doit exister si elle est indiquée. Si elle est supprimée, le champ devient NULL.

Schéma logistics :

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

La variante peut être supprimée (par exemple, retirée de la vente).

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

Tout mouvement en stock concerne un vrai produit.

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

Le type de mouvement peut concerner une variante précise du produit.

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

Le niveau de stock est défini pour des produits existants.

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

Les transferts ne concernent que des produits vraiment existants.

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

La variante peut être supprimée.

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

Chaque colis est lié à une commande précise.

Schéma payment :

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

Une transaction de paiement est toujours liée à une commande.

[payment].invoice.order_id → [order].order.id La facture est émise pour une commande.

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

L'adresse de facturation appartient à un utilisateur.

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

Le wallet est unique pour chaque utilisateur.

Schéma marketing :

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

Une remise peut être liée à un produit précis.

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

Une remise peut être liée à une catégorie de produits.

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

Remise pour une marque.

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

Les codes promo sont utilisés uniquement par des utilisateurs enregistrés.

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

Le produit mis en avant sur la vitrine doit exister.

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

Celui qui invite – utilisateur obligatoire.

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

Celui qui est invité – utilisateur obligatoire.

Schéma support :

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

Chaque ticket est créé par un utilisateur.

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

La catégorie du ticket peut être supprimée, le ticket reste.

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

Les messages sont envoyés par des utilisateurs (ou des agents, s'ils sont aussi dans user.account).

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

Un agent support est toujours un utilisateur.

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

On note qui a changé le statut (utilisateur ou employé).

Schéma content :

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

L'article est écrit par un utilisateur enregistré.

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

Qui a uploadé le fichier média (peut être supprimé).

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

Qui a fait la modif (peut être supprimé).

Remarque

Partout où c'est possible, pense à utiliser ON DELETE SET NULL, pour que quand une entité est supprimée, l'info sur les liens ne soit pas complètement perdue.

Pour relier des entités via le type "target_type/target_id" (genre user.comment), une foreign key n'est pas possible au niveau SQL, ces liens doivent être gérés côté application.

Cette liste couvre les principaux liens entre schémas pour assurer l'intégrité de base des données au niveau SQL.

Fichier avec la solution.