Foreign Keys zum Verbinden von Marketplace-Schemas

Du hast eine Datenbank, du hast Tabellen. Und du hast diese Tabellen sogar schon mit Daten gefüllt. Jetzt ist es an der Zeit, sich um die Konsistenz dieser Daten zu kümmern.

Beim Erstellen der Tabellen innerhalb jedes Schemas wurden automatisch Foreign Keys auf Tabellen im selben Schema hinzugefügt. Jetzt musst du Tabellen aus verschiedenen Schemas miteinander verbinden – füge Foreign Keys hinzu.

Zum Beispiel verweist die Tabelle order aus dem Schema order auf einen Nutzer aus der Tabelle account im Schema user. Dafür musst du einen Foreign Key hinzufügen. So könnte das SQL-Statement aussehen:

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

Liste der Foreign Keys

Insgesamt musst du 40 Foreign Keys hinzufügen :)

Schema product:

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

Produktbewertungen müssen mit den Nutzern verknüpft sein, die sie geschrieben haben.

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

Fragen zu Produkten werden von registrierten Nutzern gestellt.

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

Antworten gehören auch zu Nutzern.

Schema order:

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

Der Nutzer, der eine Bestellung aufgibt, ist immer ein registrierter Nutzer.

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

Eine Bestellposition verweist immer auf ein konkretes Produkt.

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

Wenn eine Produktvariante gewählt wurde, muss sie existieren. Aber die Variante kann gelöscht werden (dann wird das Feld NULL).

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

Der Warenkorb gehört zu einem bestimmten Nutzer.

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

Das Produkt im Warenkorb muss ein gültiges Produkt sein.

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

Die gewählte Produktvariante muss existieren, falls angegeben. Wenn sie gelöscht wurde, wird das Feld NULL.

Schema logistics:

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

Eine Variante kann gelöscht werden (zum Beispiel aus dem Sortiment genommen).

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

Jede Lagerbewegung bezieht sich auf ein echtes Produkt.

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

Die Bewegung kann sich auf eine bestimmte Produktvariante beziehen.

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

Der Lagerbestand wird für existierende Produkte festgelegt.

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

Transfers gibt es nur für wirklich existierende Produkte.

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

Eine Variante kann gelöscht werden.

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

Jedes Paket ist mit einer bestimmten Bestellung verknüpft.

Schema payment:

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

Eine Zahlungstransaktion gehört immer zu einer Bestellung.

[payment].invoice.order_id → [order].order.id Die Rechnung wird für eine Bestellung ausgestellt.

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

Die Rechnungsadresse gehört zu einem Nutzer.

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

Das Wallet ist eindeutig für einen Nutzer.

Schema marketing:

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

Ein Rabatt kann an ein bestimmtes Produkt gebunden sein.

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

Ein Rabatt kann an eine Produktkategorie gebunden sein.

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

Rabatt für eine Marke.

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

Promo-Codes werden nur von registrierten Nutzern verwendet.

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

Das ausgewählte Produkt auf der Startseite muss existieren.

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

Wer eingeladen hat – muss ein Nutzer sein.

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

Wer eingeladen wurde – muss ein Nutzer sein.

Schema support:

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

Jedes Ticket wird von einem Nutzer erstellt.

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

Die Ticket-Kategorie kann gelöscht werden, das Ticket bleibt bestehen.

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

Nachrichten werden von Nutzern gesendet (oder von Agents, falls sie auch in user.account sind).

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

Ein Support-Agent ist immer ein Nutzer.

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

Wir halten fest, wer den Status geändert hat (Nutzer oder Mitarbeiter).

Schema content:

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

Ein Artikel wird von einem registrierten Nutzer geschrieben.

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

Wer die Mediendatei hochgeladen hat (kann gelöscht werden).

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

Wer die Änderung gemacht hat (kann gelöscht werden).

Hinweis

Überall, wo es möglich ist, solltest du ON DELETE SET NULL nutzen, damit beim Löschen einer Entität die Infos zu den Verknüpfungen nicht komplett verloren gehen.

Wenn Entitäten über das Typ-System "target_type/target_id" (zum Beispiel user.comment) verbunden werden, ist ein Foreign Key auf SQL-Ebene nicht möglich. Solche Verbindungen müssen auf Applikationsebene kontrolliert werden.

Diese Liste deckt die wichtigsten Cross-Schema-Relations für grundlegende Datenkonsistenz auf SQL-Ebene ab.

Datei mit der Lösung.