Marketplace 스키마 연결을 위한 Foreign Keys

너한테는 데이터베이스가 있고, 테이블도 있어. 그리고 이미 그 테이블에 데이터를 채워넣었지. 이제 이 데이터의 무결성을 챙길 때가 된 거야.

각 스키마 안에서 테이블을 만들 때, 같은 스키마 내의 테이블에 대한 foreign keys는 자동으로 추가됐어. 이제는 서로 다른 스키마의 테이블을 연결해야 해 - foreign keys를 추가하는 거지.

예를 들어 order 스키마의 order 테이블은 user 스키마의 account 테이블에 있는 유저를 참조해. 이 테이블에 foreign key를 추가해야 해. 쿼리는 대충 이렇게 생겼을 거야:

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

Foreign Keys 목록

총 40개의 foreign key를 추가해야 해 :)

product 스키마:

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

상품에 대한 리뷰는 그걸 남긴 유저랑 연결되어야 해.

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

상품에 대한 질문은 등록된 유저만 할 수 있어.

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

답변도 유저 소유야.

order 스키마:

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

주문을 한 사람은 항상 등록된 유저야.

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

주문 아이템은 항상 특정 상품을 참조해.

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

상품의 variant가 선택됐다면, 그 variant는 존재해야 해. 근데 variant가 삭제되면 (그럼 필드는 NULL이 돼).

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

장바구니는 특정 유저의 거야.

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

장바구니에 담긴 상품은 유효한 상품이어야 해.

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

선택된 variant가 있으면 그게 존재해야 하고, 삭제되면 필드는 NULL이 돼.

logistics 스키마:

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

variant는 삭제될 수도 있어 (예: 판매 중지).

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

창고에서 일어나는 모든 움직임은 실제 상품에 해당돼.

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

움직임이 특정 상품 variant에 대한 것일 수도 있어.

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

재고 수준은 존재하는 상품 기준으로 정해져.

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

이동은 실제로 존재하는 상품만 가능해.

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

variant는 삭제될 수도 있어.

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

각 패키지는 특정 주문과 연결돼.

payment 스키마:

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

결제 트랜잭션은 항상 주문에 연결돼.

[payment].invoice.order_id → [order].order.id 인보이스는 주문 기준으로 발행돼.

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

청구 주소는 유저 소유야.

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

지갑은 유저마다 유일해.

marketing 스키마:

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

할인은 특정 상품에 연결될 수 있어.

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

할인은 상품 카테고리에 연결될 수도 있어.

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

브랜드 할인.

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

프로모코드는 등록된 유저만 쓸 수 있어.

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

메인에 노출되는 상품은 반드시 존재해야 해.

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

누가 초대했는지 - 필수 유저.

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

누가 초대받았는지 - 필수 유저.

support 스키마:

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

각 지원 티켓은 유저가 생성해.

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

티켓 카테고리는 삭제될 수 있지만, 티켓은 남아있어.

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

메시지는 유저(혹은 user.account에 있는 에이전트)가 보냄.

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

지원 에이전트도 항상 유저야.

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

누가 상태를 바꿨는지 기록해 (유저나 직원).

content 스키마:

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

글은 등록된 유저가 쓴 거야.

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

누가 미디어 파일을 올렸는지 (삭제될 수도 있음).

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

누가 수정했는지 (삭제될 수도 있음).

참고

가능한 곳에서는 ON DELETE SET NULL을 써서, 엔티티가 삭제돼도 연결 정보가 완전히 사라지지 않게 하는 게 좋아.

"target_type/target_id" 타입(예: user.comment)으로 엔티티를 연결할 때는, SQL 레벨에서 foreign key를 쓸 수 없고, 이런 연결은 애플리케이션 레벨에서 관리해야 해.

이 목록은 SQL 레벨에서 기본적인 데이터 무결성을 위한 주요 크로스-스키마 연결을 다루고 있어.

솔루션이 포함된 파일.