Modelagem de Entidade-Relacionamento

Cardinalidade, opcionalidades e convenções de nomenclatura transformam substantivos do domínio em tabelas PostgreSQL que permanecem corretas sob carga.

Receita

Cartão de referência rápida - pronto para copiar e colar.

-- Um para muitos: FK na tabela "muitos"
CREATE TABLE posts (
  post_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id  bigint NOT NULL REFERENCES authors (author_id)
);
 
-- Relacionamento opcional: FK anulável
CREATE TABLE posts (
  post_id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  editor_id     bigint REFERENCES editors (editor_id)  -- NULL = não atribuído
);
 
-- Muitos para muitos: tabela de junção com duas FKs
CREATE TABLE tag_assignments (
  post_id  bigint NOT NULL REFERENCES posts (post_id) ON DELETE CASCADE,
  tag_id   bigint NOT NULL REFERENCES tags (tag_id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

Quando usar isso:

• Design de esquema Greenfield antes da primeira migração ser enviada.
• Refatoração de um JOIN de fan-out causado pela falta de uma tabela de junção.
• Revisão se uma FK anulável ou uma tabela de link separada é a estrutura correta.
• Nomenclatura de tabelas e colunas para que os caminhos de JOIN se pareçam com o domínio.

Exemplo de Trabalho

BEGIN;
 
CREATE TABLE authors (
  author_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL
);
 
CREATE TABLE editors (
  editor_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text NOT NULL
);
 
CREATE TABLE posts (
  post_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id   bigint NOT NULL REFERENCES authors (author_id),
  editor_id   bigint REFERENCES editors (editor_id),
  title       text NOT NULL,
  published   boolean NOT NULL DEFAULT false
);
 
CREATE TABLE tags (
  tag_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  label   text NOT NULL UNIQUE
);
 
CREATE TABLE post_tags (
  post_id  bigint NOT NULL REFERENCES posts (post_id) ON DELETE CASCADE,
  tag_id   bigint NOT NULL REFERENCES tags (tag_id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);
 
CREATE INDEX posts_author_id_idx ON posts (author_id);
CREATE INDEX posts_editor_id_idx ON posts (editor_id) WHERE editor_id IS NOT NULL;
CREATE INDEX post_tags_tag_id_idx ON post_tags (tag_id);
 
COMMIT;

O que isso demonstra:

• Relacionamentos obrigatórios vs. opcionais através de colunas FK NOT NULL vs. anuláveis.
• M:N resolvido com post_tags em vez de duplicar linhas de tag em posts.
• Índice parcial em editor_id opcional mantém o tamanho do índice pequeno quando a maioria dos posts não tem editor.
• ON DELETE CASCADE em linhas de junção quando posts pai ou tags são removidos.

Mergulho Profundo

Cardinalidade em Resumo

Convenções de Nomenclatura

• Tabelas: substantivos no plural (orders, line_items) ou singular se sua organização padroniza em singular - escolha um e aplique lint.
• Colunas PK: <entidade>_id (order_id) com bigint GENERATED ALWAYS AS IDENTITY.
• Colunas FK: correspondem ao nome da PK referenciada (customer_id referencia customers.customer_id).
• Tabelas de junção: <esquerda>_<direita> ou <esquerda>_<direita>_map (order_items, user_roles).

Regras de Opcionalidade

-- Todo pedido deve ter um cliente (obrigatório)
customer_id bigint NOT NULL REFERENCES customers (customer_id)
 
-- O envio pode ainda não existir (opcional)
shipment_id bigint REFERENCES shipments (shipment_id)

• Relacionamentos obrigatórios: NOT NULL + FK.
• Relacionamentos opcionais: FK anulável; use índices parciais para buscas seletivas.
• Não modele a opcionalidade apenas no código da aplicação - o banco de dados deve rejeitar estados impossíveis.

Notas SQL

-- Detecta linhas órfãs se as FKs foram adicionadas tarde (deve retornar 0)
SELECT o.order_id
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;
 
-- 1:1 imposto com UNIQUE na coluna FK
CREATE TABLE user_profiles (
  user_id    bigint PRIMARY KEY REFERENCES users (user_id),
  bio        text
);

Armadilhas

• M:N modelado como colunas duplicadas - armazenar tag1, tag2, tag3 em posts quebra a normalização e impede a indexação. Correção: tabela de junção com (post_id, tag_id).
• FK no lado errado - colocar order_id em customers para uma lista de pedidos 1:N. Correção: FK em orders.customer_id.
• FK anulável tratada como obrigatória em consultas - INNER JOINs removem linhas com editores não atribuídos. Correção: LEFT JOIN quando o relacionamento é opcional.
• Índice ausente em colunas FK - NESTED LOOP JOINs degradam em tabelas grandes. Correção: CREATE INDEX ON child (parent_id).
• Junção simétrica sem unicidade - pares duplicados de (post_id, tag_id). Correção: PRIMARY KEY composto ou restrição UNIQUE.

Alternativas

FAQs

CREATE TABLE passports (
  passport_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  person_id    bigint NOT NULL UNIQUE REFERENCES persons (person_id),
  number       text NOT NULL
);

CONSTRAINT orders_customer_id_fkey
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id)

CREATE TABLE users (user_id bigint PRIMARY KEY, email text NOT NULL);
CREATE TABLE user_settings (
  user_id bigint PRIMARY KEY REFERENCES users (user_id),
  theme   text NOT NULL DEFAULT 'light'
);

Relacionados

• Noções Básicas de Modelagem de Dados - fluxo do conceitual ao físico
• Limites de Esquema Orientados ao Domínio - agrupamento de entidades por contexto
• Noções Básicas de Normalização - eliminação de anomalias de atualização
• Chaves Estrangeiras Ausentes - falhas de integridade sem FKs
• Explosão de Join Fan-Out - erros de cardinalidade em consultas

Versões do Stack: Esta página foi escrita para PostgreSQL 18.4 (estável 18, manutenção 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, e PostGIS 3.5+.