Foreign Keys per collegare gli schemi di Marketplace

Hai un database, hai delle tabelle. E hai pure già riempito queste tabelle con dei dati. Ora è il momento di preoccuparti dell'integrità di questi dati.

Quando hai creato le tabelle dentro ogni schema, sono stati aggiunti automaticamente i foreign keys verso le tabelle dello stesso schema. Ora ti serve collegare tra loro le tabelle di schemi diversi - aggiungere i foreign keys.

Per esempio, la tabella order dello schema order fa riferimento a un utente dalla tabella account dello schema user. Devi aggiungere un foreign key per questa tabella. Una query del genere potrebbe essere così:

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

Lista dei foreign keys

In totale dovrai aggiungere 40 foreign keys :)

Schema product:

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

Le recensioni sui prodotti devono essere collegate agli utenti che le hanno lasciate.

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

Le domande sui prodotti vengono fatte dagli utenti registrati.

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

Anche le risposte appartengono agli utenti.

Schema order:

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

L'utente che fa un ordine è sempre un utente registrato.

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

Una riga d'ordine fa sempre riferimento a un prodotto specifico.

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

Se viene scelto un variant del prodotto — deve esistere. Ma il variant può essere eliminato (in quel caso il campo diventa NULL).

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

Il carrello appartiene a un utente specifico.

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

Il prodotto nel carrello deve essere un prodotto valido.

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

Il variant scelto deve esistere, se specificato. Se viene eliminato — il campo diventa NULL.

Schema logistics:

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

Il variant può essere eliminato (ad esempio, tolto dalla vendita).

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

Ogni movimento in magazzino riguarda un prodotto reale.

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

Il tipo di movimento può essere per un variant specifico del prodotto.

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

Il livello delle scorte viene impostato sui prodotti esistenti.

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

I trasferimenti sono solo per prodotti realmente esistenti.

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

Il variant può essere eliminato.

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

Ogni pacco è collegato a un ordine specifico.

Schema payment:

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

La transazione di pagamento è sempre collegata a un ordine.

[payment].invoice.order_id → [order].order.id La fattura viene emessa per un ordine.

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

L'indirizzo di fatturazione appartiene a un utente.

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

Il wallet è unico per ogni utente.

Schema marketing:

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

Lo sconto può essere collegato a un prodotto specifico.

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

Lo sconto può essere collegato a una categoria di prodotti.

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

Sconto per il brand.

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

I codici promo vengono usati solo dagli utenti registrati.

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

Il prodotto in vetrina deve esistere.

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

Chi invita — utente obbligatorio.

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

Chi viene invitato — utente obbligatorio.

Schema support:

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

Ogni ticket viene creato da un utente.

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

La categoria del ticket può essere eliminata, il ticket resta.

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

I messaggi vengono inviati dagli utenti (o dagli agenti, se anche loro sono in user.account).

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

L'agente di supporto è sempre un utente.

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

Registriamo chi ha cambiato lo status (utente o staff).

Schema content:

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

L'articolo è scritto da un utente registrato.

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

Chi ha caricato il file media (può essere eliminato).

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

Chi ha fatto la modifica (può essere eliminato).

Nota

Dove possibile, conviene prevedere ON DELETE SET NULL, così quando elimini una entità non perdi completamente le info sui collegamenti.

Per collegare entità tramite il tipo "target_type/target_id" (tipo user.comment), il foreign key non è possibile a livello SQL, questi collegamenti vanno gestiti a livello di applicazione.

Questa lista copre i principali collegamenti cross-schema per l'integrità di base dei dati a livello SQL.

File con la soluzione.