Foreign Keys para conectar schemas do Marketplace

Você já tem um banco de dados, já tem tabelas. E até já preencheu essas tabelas com dados. Agora chegou a hora de garantir a integridade desses dados.

Quando você criou as tabelas dentro de cada schema, as foreign keys pras tabelas do mesmo schema já foram criadas automaticamente. Agora você precisa conectar tabelas de schemas diferentes – adicionar foreign keys.

Por exemplo, a tabela order do schema order referencia o usuário da tabela account do schema user. Você precisa adicionar uma foreign key pra essa tabela. O comando pode ficar mais ou menos assim:

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

Lista de foreign keys

No total, você vai precisar adicionar 40 foreign keys :)

Schema product:

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

Os reviews dos produtos têm que estar ligados aos usuários que escreveram eles.

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

Perguntas sobre o produto são feitas por usuários cadastrados.

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

As respostas também pertencem aos usuários.

Schema order:

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

O usuário que fez o pedido é sempre um usuário cadastrado.

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

O item do pedido sempre referencia um produto específico.

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

Se um variant do produto foi escolhido, ele tem que existir. Mas o variant pode ser removido (aí o campo vira NULL).

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

O carrinho pertence a um usuário específico.

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

O produto no carrinho tem que ser um produto válido.

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

O variant escolhido do produto tem que existir, se estiver preenchido. Se for removido, o campo vira NULL.

Schema logistics:

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

O variant pode ser removido (tipo, saiu de linha).

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

Qualquer movimentação no estoque é pra um produto real.

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

O tipo de movimentação pode ser pra um variant específico do produto.

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

O nível de estoque é definido pra produtos que existem.

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

Transferências só pra produtos que existem de verdade.

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

O variant pode ser removido.

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

Cada pacote está ligado a um pedido específico.

Schema payment:

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

A transação de pagamento sempre está ligada a um pedido.

[payment].invoice.order_id → [order].order.id A fatura é gerada pra um pedido.

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

O endereço de cobrança pertence ao usuário.

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

A wallet é única pra cada usuário.

Schema marketing:

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

O desconto pode ser pra um produto específico.

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

O desconto pode ser pra uma categoria de produtos.

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

Desconto pra marca.

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

Promo codes só podem ser usados por usuários cadastrados.

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

O produto em destaque na vitrine tem que existir.

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

Quem convidou – tem que ser usuário obrigatório.

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

Quem foi convidado – também tem que ser usuário obrigatório.

Schema support:

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

Cada chamado é criado por um usuário.

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

A categoria do chamado pode ser removida, mas o chamado fica.

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

As mensagens são enviadas por usuários (ou agentes, se eles também estiverem em user.account).

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

O agente de suporte é sempre um usuário.

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

A gente registra quem mudou o status (usuário ou funcionário).

Schema content:

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

O artigo foi escrito por um usuário cadastrado.

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

Quem fez o upload do arquivo de mídia (pode ser removido).

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

Quem fez a alteração (pode ser removido).

Observação

Onde der, é bom usar ON DELETE SET NULL, assim quando uma entidade for removida, a info das ligações não some totalmente.

Pra ligar entidades usando o tipo "target_type/target_id" (tipo user.comment), não dá pra usar foreign key no SQL, essas ligações têm que ser controladas na aplicação.

Essa lista cobre as principais ligações entre schemas pra garantir a integridade básica dos dados no SQL.

Ficheiro com a solução.