Foreign Keys do łączenia schematów Marketplace

Masz bazę danych, masz tabele. I nawet już wypełniłeś te tabele danymi. Teraz przyszedł czas, żeby zadbać o spójność tych danych.

Podczas tworzenia tabel w każdym schemacie automatycznie zostały dodane foreign keys do tabel z tego samego schematu. Teraz musisz połączyć tabele z różnych schematów - dodać foreign keys.

Na przykład tabela order ze schematu order odnosi się do użytkownika z tabeli account ze schematu user. Musisz dodać foreign key do tej tabeli. Taki query może wyglądać mniej więcej tak:

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

Lista foreign keys

Łącznie musisz dodać 40 foreign keys :)

Schemat product:

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

Opinie o produktach muszą być powiązane z użytkownikami, którzy je zostawili.

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

Pytania o produkt zadają zarejestrowani użytkownicy.

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

Odpowiedzi też należą do użytkowników.

Schemat order:

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

Użytkownik, który złożył zamówienie — zawsze zarejestrowany user.

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

Pozycja zamówienia zawsze odnosi się do konkretnego produktu.

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

Jeśli wybrano wariant produktu — musi istnieć. Ale wariant może być usunięty (wtedy pole będzie NULL).

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

Koszyk należy do konkretnego użytkownika.

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

Produkt w koszyku musi być poprawnym produktem.

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

Wybrany wariant produktu musi istnieć, jeśli jest podany. Jeśli usunięty — pole staje się NULL.

Schemat logistics:

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

Wariant może być usunięty (np. wycofany ze sprzedaży).

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

Każdy ruch w magazynie dotyczy realnego produktu.

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

Ruch może dotyczyć konkretnego wariantu produktu.

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

Poziom zapasów ustalany jest dla istniejących produktów.

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

Przeniesienia tylko dla realnie istniejącego produktu.

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

Wariant może być usunięty.

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

Każde opakowanie powiązane jest z konkretnym zamówieniem.

Schemat payment:

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

Transakcja płatności zawsze dotyczy zamówienia.

[payment].invoice.order_id → [order].order.id Faktura wystawiana jest do zamówienia.

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

Adres rozliczeniowy należy do użytkownika.

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

Portfel jest unikalny dla użytkownika.

Schemat marketing:

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

Zniżka może być przypisana do konkretnego produktu.

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

Zniżka może być przypisana do kategorii produktów.

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

Zniżka dla marki.

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

Promokody używają tylko zarejestrowani użytkownicy.

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

Wyróżniony produkt na stronie głównej musi istnieć.

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

Kto zaprosił — obowiązkowy użytkownik.

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

Kogo zaproszono — obowiązkowy użytkownik.

Schemat support:

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

Każde zgłoszenie tworzy użytkownik.

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

Kategoria zgłoszenia może być usunięta, zgłoszenie zostaje.

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

Wiadomości wysyłają użytkownicy (albo agenci, jeśli też są w user.account).

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

Agent supportu — to zawsze użytkownik.

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

Zapisujemy, kto zmienił status (user albo pracownik).

Schemat content:

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

Artykuł napisany przez zarejestrowanego użytkownika.

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

Kto wrzucił plik multimedialny (może być usunięty).

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

Kto zrobił zmianę (może być usunięty).

Uwaga

Wszędzie, gdzie się da, warto ustawić ON DELETE SET NULL, żeby przy usuwaniu encji info o powiązaniach nie znikało całkiem.

Do łączenia encji przez typ "target_type/target_id" (np. user.comment), foreign key nie jest możliwy na poziomie SQL, takie powiązania trzeba ogarniać w aplikacji.

Ta lista obejmuje główne powiązania między schematami dla podstawowej spójności danych na poziomie SQL.

Plik z rozwiązaniem.