---
id: schema-modeling-decisions
domain: data
agents: [data-engineer]
when: "ao modelar o schema de uma aplicação"
---

# Decisões de modelagem de schema — normalizar, chaves, relacionamentos e árvores

A maioria dos schemas ruins não nasce de ignorância: nasce de defaults aplicados sem critério.
Coluna `id` serial em toda tabela porque o ORM gera; lista separada por vírgula porque "é só um
campo"; `parent_id` numa árvore porque era o primeiro padrão que apareceu. Bill Karwin catalogou
esses defaults como **antipatterns** — cada um tem uma cara reconhecível e uma correção concreta.
Este pack é a árvore de decisão de modelagem: quando normalizar, qual chave usar, como traduzir
cardinalidade em FK, e como modelar hierarquia sem cair no antipattern. A régua-mãe (Karwin):
**"A Primary Key is a constraint, not a data type"** — e o mesmo vale para todo o schema. Você
escolhe a estrutura pela restrição que ela garante, não pelo que o framework gera por padrão.

## O problema (os tells de um schema gerado, não desenhado)

Reconheça para evitar. Se o schema tem 3+ destes, ele foi gerado, não modelado:

1. **Jaywalking** — múltiplos valores numa coluna separados por vírgula (`tags = "sql,db,perf"`,
   `account_ids = "12,34,56"`). Viola a 1NF. Karwin chama de *jaywalking* porque é "atravessar fora
   da faixa" para evitar criar a tabela de interseção (*intersection*).
2. **Multicolumn Attributes** — `tag1, tag2, tag3` em colunas separadas. Mesmo problema da
   Jaywalking, só que na horizontal: como você consulta "todas as linhas com a tag X" sem repetir
   `WHERE tag1=X OR tag2=X OR tag3=X`?
3. **ID Required** — coluna `id` serial em *toda* tabela por reflexo, inclusive onde uma chave
   natural ou composta seria melhor (sobretudo em tabelas de interseção).
4. **Keyless Entry** — relacionamentos sem `FOREIGN KEY`. A integridade vira responsabilidade da
   aplicação, e qualquer script externo (migração, import, job) gera linhas órfãs.
5. **Naive Trees** — hierarquia modelada só com `parent_id` (adjacency list) sem pensar no padrão
   de consulta. "Pegar todos os descendentes" vira N joins ou recursão na aplicação.
6. **EAV (Entity-Attribute-Value)** — tabela genérica `entity_id | attribute_name | value` para
   "schema flexível". Você perde tipo, `NOT NULL`, FK e qualquer consulta sã.
7. **Polymorphic Associations** — uma FK que aponta para "várias tabelas" via coluna
   discriminadora (`commentable_id` + `commentable_type`). SQL não suporta FK assim → sem
   integridade referencial.

## O conhecimento / os princípios

### 1. Normalizar é o default. Desnormalizar é uma exceção justificada.

Normalize até a 3NF como ponto de partida. As três primeiras formas normais resolvem os
antipatterns mais comuns:

| Forma | Regra (Karwin) | Antipattern que elimina |
|---|---|---|
| **1NF** | Sem colunas repetidas nem valores separados por vírgula numa célula. Cada célula = um valor atômico. | Jaywalking, Multicolumn Attributes |
| **2NF** | Com chave composta, nenhuma coluna pode depender de só *parte* da chave. | Redundância em tabelas de junção |
| **3NF** | Proíbe armazenar dado não relacionado à PK e duplicado em outro lugar. | Anomalias de update |

> Karwin: ir além de 3NF/BCNF para 4NF+ é raramente necessário e custa muitos joins. 3NF é o alvo
> prático para a maioria das aplicações.

**Desnormalize só com critério explícito** (não "por performance" no chute). Critérios válidos:

- **Read-heavy comprovado** — a tabela é lida ordens de magnitude mais do que escrita, e o join
  está medido como gargalo (não suposto).
- **Dado histórico/snapshot** — você *quer* congelar o valor no momento (preço da `order_item` na
  hora da compra, não o preço atual do produto). Aqui "duplicar" o preço não é desnormalização — é
  o modelo correto, porque a semântica é "valor naquele instante".
- **Agregado materializado** — `COUNT`/`SUM` pré-calculado (ex.: `post.comments_count`) com
  trigger ou job mantendo a consistência.

Regra: se você não consegue nomear **qual escrita vai manter o dado redundante consistente**, não
desnormalize. A redundância sem dono é bug esperando acontecer.

### 2. Jaywalking / Multicolumn → tabela de interseção (1NF)

O tell:

```sql
-- ANTIPATTERN (Jaywalking): N:N escondido numa string
CREATE TABLE products (
  product_id  SERIAL PRIMARY KEY,
  account_ids VARCHAR(100)   -- "12,34,56"
);
-- "Quem é dono do produto 5?"  →  WHERE account_ids LIKE '%5%'  (pega 15, 25, 51...)
-- COUNT/SUM/JOIN/FK: impossíveis. Limite de tamanho da coluna: bomba-relógio.
```

A correção é sempre a **tabela de interseção**, com a chave composta dos dois lados como PK
(que também elimina duplicatas de graça):

```sql
CREATE TABLE contacts (
  product_id  INT NOT NULL REFERENCES products(product_id),
  account_id  INT NOT NULL REFERENCES accounts(account_id),
  PRIMARY KEY (product_id, account_id)   -- chave composta natural, sem 'id' serial
);
```

O mesmo vale para `tag1, tag2, tag3` (Multicolumn): vira `post_tags(post_id, tag_id)`.

### 3. PK: surrogate (pseudokey) vs. natural vs. composta

Karwin: o antipattern **ID Required** não é "usar `id` serial" — é usar `id` serial *sem pensar*,
em toda tabela. A decisão real:

```sql
-- Tabela de interseção: chave COMPOSTA NATURAL é melhor que 'id' serial.
-- Garante unicidade do par (impede duplicata) sem índice extra.
PRIMARY KEY (product_id, account_id)

-- Entidade de domínio com identificador natural confiável e estável:
CREATE TABLE countries (
  iso_code CHAR(2) PRIMARY KEY,   -- 'BR', 'US' — único, estável, significativo
  name     VARCHAR(80) NOT NULL
);

-- Entidade sem chave natural estável (usuário, pedido): surrogate é legítimo.
CREATE TABLE orders (
  order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);
```

Critérios de Karwin para **natural/composta**:
- O atributo é **genuinamente único** (verifique nos dados reais, não na esperança).
- É **estável** (não muda — CPF muda? email muda? então não é boa PK).
- Carrega **significado de domínio** que simplifica queries.

Critérios para **surrogate**:
- Não existe chave natural estável, OU a natural é larga/composta e referenciada por muitas FKs.
- A imutabilidade da PK importa (surrogate nunca muda, mesmo que o "negócio" mude).

> Nota de naming: Karwin recomenda nome descritivo (`comment_id`) para permitir `JOIN ... USING
> (comment_id)`. Equipes que abstraem o front com convenção `id` constante discordam — escolha uma
> convenção e mantenha. O antipattern é não decidir.

### 4. Cardinalidade → FK (e sempre declare a FK)

Traduza a cardinalidade do domínio em estrutura. E declare `FOREIGN KEY` — **Keyless Entry** é
antipattern: sem a constraint, a integridade vira "torcer para a aplicação acertar", e qualquer
import/script externo gera órfãos.

| Cardinalidade | Onde mora a FK | Forma |
|---|---|---|
| **1:N** (um cliente, N pedidos) | Na tabela do lado "N" | `orders.customer_id REFERENCES customers` |
| **N:N** (produtos ↔ contas) | Tabela de interseção | `contacts(product_id, account_id)` com PK composta |
| **1:1** (user ↔ profile) | FK + `UNIQUE` (ou PK compartilhada) | `profiles.user_id UNIQUE REFERENCES users` |
| **0..1** (opcional) | FK `NULL`-able | `employees.manager_id NULL REFERENCES employees` |

Escolha a ação referencial de propósito (Karwin):

```sql
order_id BIGINT REFERENCES orders(order_id)
  ON DELETE CASCADE     -- itens somem com o pedido (parte fraca da composição)
-- ON DELETE RESTRICT   -- impede apagar o pai se há filhos (default seguro)
-- ON DELETE SET NULL   -- desvincula sem apagar (ex.: manager saiu, subordinado fica)
```

### 5. Hierarquias → escolha o modelo pelo padrão de leitura/escrita (não use só `parent_id`)

**Naive Trees** = usar só adjacency list sem perguntar como a árvore é consultada. Os quatro
modelos de Karwin, com critério de escolha:

**a) Adjacency List** (`parent_id`) — o default honesto.

```sql
CREATE TABLE comments (
  comment_id SERIAL PRIMARY KEY,
  parent_id  INT REFERENCES comments(comment_id),
  body       TEXT
);
-- Mover subárvore = 1 UPDATE no parent_id. Inserir = trivial.
-- "Todos os descendentes" precisa de recursão (PostgreSQL 8.2+):
WITH RECURSIVE tree AS (
  SELECT comment_id, parent_id FROM comments WHERE comment_id = 4
  UNION ALL
  SELECT c.comment_id, c.parent_id
  FROM comments c JOIN tree t ON c.parent_id = t.comment_id
)
SELECT * FROM tree;
```
Use quando: o banco suporta `WITH RECURSIVE`, inserts/moves são frequentes, e a profundidade das
leituras é moderada. Em Postgres moderno, é a escolha default razoável.

**b) Path Enumeration** (caminho materializado) — leitura por prefixo.

```sql
ALTER TABLE comments ADD COLUMN path VARCHAR(255);  -- '4/5/8/'
-- Todos os descendentes de 4 (sem recursão):
SELECT * FROM comments WHERE path LIKE '4/%';
```
Use quando: leitura é dominante e por subárvore. Cuidado: mover uma subárvore exige reescrever o
`path` de todos os descendentes, e não há FK garantindo que o caminho aponta para nós reais.

**c) Nested Sets** (`left`/`right`) — leitura de subárvore ultrarrápida, escrita cara.

Cada nó guarda dois números (left, right); descendentes têm `left/right` *entre* os do ancestral.
Karwin: rápido para consultar subárvores, mas **inserir/mover é complexo** — quase todos os nós
precisam ter left/right renumerados. Use só quando a árvore é praticamente read-only (catálogo
estável) e você lê subárvores inteiras o tempo todo.

**d) Closure Table** — tabela separada com *uma linha por par ancestral→descendente*. O equilíbrio
de Karwin: rápido em todas as operações, ao custo de mais armazenamento.

```sql
CREATE TABLE comment_tree (
  ancestor   INT NOT NULL REFERENCES comments(comment_id),
  descendant INT NOT NULL REFERENCES comments(comment_id),
  depth      INT NOT NULL,
  PRIMARY KEY (ancestor, descendant)
);
-- Inserir nó: linha self (depth 0) + uma linha para cada ancestral do pai.
INSERT INTO comment_tree (ancestor, descendant, depth)
  SELECT ancestor, :new_id, depth + 1
  FROM comment_tree WHERE descendant = :parent_id
  UNION ALL SELECT :new_id, :new_id, 0;
-- Todos os descendentes: SELECT descendant FROM comment_tree WHERE ancestor = :id;
-- Linha de comando ascendente: SELECT ancestor FROM comment_tree WHERE descendant = :id ORDER BY depth DESC;
```
Use quando: você precisa de consultas rápidas de ancestral *e* descendente, suporta atributos na
aresta (ex.: data de vigência), e as escritas são moderadas. É a escolha mais versátil do livro.

**e) `ltree` (PostgreSQL)** — path enumeration nativo, com operadores de árvore.

```sql
CREATE EXTENSION ltree;
ALTER TABLE comments ADD COLUMN path ltree;   -- 'root.4.5.8'
CREATE INDEX ON comments USING GIST (path);
SELECT * FROM comments WHERE path <@ '4';      -- todos os descendentes de 4
```
Use quando: está em Postgres e quer materialized path "de fábrica". Limitações: o índice GIST tem
limite de tamanho de label (árvores muito profundas sofrem), e mover subárvore exige reescrever o
`path` de todos os descendentes.

## Checklist (passe antes de aprovar o schema)

- [ ] Alguma coluna guarda lista separada por vírgula ou `campo1/campo2/campo3`? (Jaywalking /
      Multicolumn → tabela de interseção)
- [ ] Toda relação tem `FOREIGN KEY` declarada com ação `ON DELETE`/`ON UPDATE` escolhida de
      propósito? (Keyless Entry)
- [ ] Cada tabela de interseção usa **chave composta** dos dois lados como PK, não um `id` serial
      desnecessário? (ID Required)
- [ ] Onde existe chave natural estável e única, ela foi considerada como PK em vez de surrogate
      automático?
- [ ] Existe alguma FK "polimórfica" (`*_id` + `*_type`)? Se sim, refatorada para arco exclusivo ou
      interseções por tipo?
- [ ] Existe tabela genérica `entity/attribute/value`? Se sim, substituída por colunas tipadas,
      tabela por subtipo, ou JSON tipado?
- [ ] A hierarquia foi modelada pelo padrão de leitura/escrita real, não só `parent_id` por reflexo?
- [ ] Toda redundância (dado duplicado) tem um dono explícito — a escrita que a mantém consistente?

## Tabela de decisão "use X quando Y"

| Use X | Quando Y |
|---|---|
| **Normalizar até 3NF** | Default. Sempre comece aqui. |
| **Desnormalizar** | Read-heavy *medido*, snapshot histórico, ou agregado materializado **com dono de consistência** |
| **Tabela de interseção** (PK composta) | Cardinalidade N:N — sempre, nunca lista CSV nem `tag1/tag2/tag3` |
| **PK surrogate** (`IDENTITY`/serial) | Sem chave natural estável; natural muito larga; imutabilidade da PK importa |
| **PK natural** | Atributo único, estável e significativo (`iso_code`, código de referência) |
| **PK composta** | Tabela de interseção (os dois FKs juntos) |
| **FK `ON DELETE CASCADE`** | Filho é parte fraca da composição (item do pedido some com o pedido) |
| **FK `ON DELETE RESTRICT`** | Default seguro: impede apagar pai com filhos |
| **FK `ON DELETE SET NULL`** | Vínculo opcional que pode existir sozinho (subordinado sem gestor) |
| **Adjacency List** (`parent_id`) | Postgres com `WITH RECURSIVE`; inserts/moves frequentes; profundidade moderada |
| **Path Enumeration / `ltree`** | Leitura por subárvore dominante; árvore não muito profunda; moves raros |
| **Nested Sets** | Árvore quase read-only; leitura de subárvore inteira é o caso crítico |
| **Closure Table** | Precisa de queries rápidas de ancestral E descendente; atributos na aresta; escrita moderada |
| **Colunas tipadas / tabela por subtipo** | Em vez de EAV — quando os atributos são conhecidos |
| **JSON/JSONB tipado** | Schema genuinamente dinâmico — em vez de EAV, mantendo o resto relacional |
| **Arco exclusivo** (parent table compartilhada) | Em vez de FK polimórfica — quando precisa de FK real |
| **Interseções por tipo** | Em vez de FK polimórfica — quando a 1:1 não precisa ser forçada |

---

Fonte: Bill Karwin, *SQL Antipatterns: Avoiding the Pitfalls of Database Programming* (Pragmatic
Bookshelf) — capítulos Jaywalking, Multicolumn Attributes, Naive Trees, ID Required, Keyless Entry,
Entity-Attribute-Value, Polymorphic Associations; e a coletânea *awesome-database-design*.