Modelagem e normalização

O esquema desta página foi criado num Postgres 18, e o código foi executado nas cinco linguagens contra ele.

O esquema da loja

CREATE TABLE clientes (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email     text NOT NULL,
  nome      text NOT NULL CHECK (length(trim(nome)) > 0),
  criado_em timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX clientes_email_unico ON clientes (lower(email));

CREATE TABLE produtos (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  nome  text NOT NULL,
  preco numeric(12, 2) NOT NULL CHECK (preco >= 0)
);

CREATE TABLE pedidos (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  cliente_id bigint NOT NULL REFERENCES clientes (id),
  status     text NOT NULL DEFAULT 'aberto'
             CHECK (status IN ('aberto', 'pago', 'enviado', 'cancelado')),
  criado_em  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON pedidos (cliente_id); -- o Postgres não cria índice para a chave estrangeira

-- Muitos para muitos: um pedido tem vários produtos, um produto está em vários pedidos
CREATE TABLE itens_pedido (
  pedido_id      bigint NOT NULL REFERENCES pedidos (id) ON DELETE CASCADE,
  produto_id     bigint NOT NULL REFERENCES produtos (id),
  quantidade     int NOT NULL CHECK (quantidade > 0),
  preco_unitario numeric(12, 2) NOT NULL, -- o preço na hora da compra, de propósito
  PRIMARY KEY (pedido_id, produto_id)
);
CREATE INDEX ON itens_pedido (produto_id);

Repare nas decisões do nível Soluções aplicadas:

bigint gerado pelo banco como chave primária, e o e-mail como UNIQUE, e não como chave.
Um índice único em lower(email), para Ana@Exemplo.com e ana@exemplo.com serem o mesmo cliente.
numeric(12, 2) para dinheiro e timestamptz para datas.
CHECK na lista de status, no preço e na quantidade.
Índices nas chaves estrangeiras pedidos.cliente_id e itens_pedido.produto_id. A chave primária composta de itens_pedido já serve às buscas por pedido_id, porque ele é a primeira coluna.
ON DELETE CASCADE só nos itens, que não existem sem o pedido. Um cliente com pedidos não pode ser apagado.
preco_unitario repetido no item, de propósito.

Traduzir a recusa do banco, em cada linguagem

O banco recusa o que viola as regras. A aplicação traduz cada recusa numa resposta: o e-mail repetido vira um 409, o dado inválido vira um 422. O Postgres informa o tipo da recusa por um código (SQLSTATE) e o nome da restrição.

import pg from "pg";

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

type Resultado =
  | { tipo: "criado"; id: number }
  | { tipo: "conflito" | "invalido" | "referencia_inexistente"; detalhe: string };

// O banco garante as regras; a aplicação traduz a recusa numa resposta
function traduzir(erro: any): Resultado {
  switch (erro.code) {
    case "23505": return { tipo: "conflito", detalhe: erro.constraint }; // UNIQUE
    case "23514": return { tipo: "invalido", detalhe: erro.constraint }; // CHECK
    case "23503": // FOREIGN KEY
      return { tipo: "referencia_inexistente", detalhe: erro.constraint };
    default: throw erro;
  }
}

async function cadastrarCliente(email: string, nome: string): Promise<Resultado> {
  try {
    const { rows } = await pool.query(
      "INSERT INTO clientes (email, nome) VALUES ($1, $2) RETURNING id", [email, nome]);
    return { tipo: "criado", id: Number(rows[0].id) };
  } catch (erro) {
    return traduzir(erro);
  }
}

async function abrirPedido(clienteId: number): Promise<Resultado> {
  try {
    const { rows } = await pool.query(
      "INSERT INTO pedidos (cliente_id) VALUES ($1) RETURNING id", [clienteId]);
    return { tipo: "criado", id: Number(rows[0].id) };
  } catch (erro) {
    return traduzir(erro);
  }
}

console.log(await cadastrarCliente("ana@exemplo.com", "Ana"));
console.log(await cadastrarCliente("ANA@exemplo.com", "Ana Maria")); // mesmo e-mail
console.log(await cadastrarCliente("bia@exemplo.com", "   "));       // nome vazio
console.log(await abrirPedido(999_999));                             // cliente inexistente
await pool.end();

import os

import psycopg
from psycopg import errors

conexao = psycopg.connect(os.environ["DATABASE_URL"], autocommit=True)


# O banco garante as regras; a aplicação traduz a recusa numa resposta
def traduzir(erro: psycopg.Error) -> dict:
    restricao = erro.diag.constraint_name
    if isinstance(erro, errors.UniqueViolation):
        return {"tipo": "conflito", "detalhe": restricao}
    if isinstance(erro, errors.CheckViolation):
        return {"tipo": "invalido", "detalhe": restricao}
    if isinstance(erro, errors.ForeignKeyViolation):
        return {"tipo": "referencia_inexistente", "detalhe": restricao}
    raise erro


def cadastrar_cliente(email: str, nome: str) -> dict:
    try:
        linha = conexao.execute(
            "INSERT INTO clientes (email, nome) VALUES (%s, %s) RETURNING id", (email, nome)
        ).fetchone()
        return {"tipo": "criado", "id": linha[0]}
    except psycopg.IntegrityError as erro:
        return traduzir(erro)


def abrir_pedido(cliente_id: int) -> dict:
    try:
        linha = conexao.execute(
            "INSERT INTO pedidos (cliente_id) VALUES (%s) RETURNING id", (cliente_id,)
        ).fetchone()
        return {"tipo": "criado", "id": linha[0]}
    except psycopg.IntegrityError as erro:
        return traduzir(erro)


print(cadastrar_cliente("ana@exemplo.com", "Ana"))
print(cadastrar_cliente("ANA@exemplo.com", "Ana Maria"))  # mesmo e-mail
print(cadastrar_cliente("bia@exemplo.com", "   "))  # nome vazio
print(abrir_pedido(999_999))  # cliente inexistente

package main

import (
	"context"
	"errors"
	"fmt"
	"os"

	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"
)

type Resultado struct {
	Tipo    string
	ID      int64
	Detalhe string
}

// O banco garante as regras; a aplicação traduz a recusa numa resposta
func traduzir(err error) (Resultado, error) {
	var pgErr *pgconn.PgError
	if !errors.As(err, &pgErr) {
		return Resultado{}, err
	}
	switch pgErr.Code {
	case "23505": // UNIQUE
		return Resultado{Tipo: "conflito", Detalhe: pgErr.ConstraintName}, nil
	case "23514": // CHECK
		return Resultado{Tipo: "invalido", Detalhe: pgErr.ConstraintName}, nil
	case "23503": // FOREIGN KEY
		return Resultado{Tipo: "referencia_inexistente", Detalhe: pgErr.ConstraintName}, nil
	}
	return Resultado{}, err
}

func inserir(ctx context.Context, db *pgxpool.Pool, sql string, args ...any) (Resultado, error) {
	var id int64
	if err := db.QueryRow(ctx, sql, args...).Scan(&id); err != nil {
		return traduzir(err)
	}
	return Resultado{Tipo: "criado", ID: id}, nil
}

func main() {
	ctx := context.Background()
	db, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
	if err != nil {
		panic(err)
	}
	defer db.Close()

	cliente := "INSERT INTO clientes (email, nome) VALUES ($1, $2) RETURNING id"
	pedido := "INSERT INTO pedidos (cliente_id) VALUES ($1) RETURNING id"
	for _, caso := range []struct {
		sql  string
		args []any
	}{
		{cliente, []any{"ana@exemplo.com", "Ana"}},
		{cliente, []any{"ANA@exemplo.com", "Ana Maria"}}, // mesmo e-mail
		{cliente, []any{"bia@exemplo.com", "   "}},       // nome vazio
		{pedido, []any{999_999}},                          // cliente inexistente
	} {
		r, err := inserir(ctx, db, caso.sql, caso.args...)
		fmt.Printf("%+v %v\n", r, err)
	}
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.postgresql.util.PSQLException;

public class Cadastro {
    record Resultado(String tipo, long id, String detalhe) {}

    // O banco garante as regras; a aplicação traduz a recusa numa resposta
    static Resultado traduzir(SQLException erro) throws SQLException {
        String restricao = erro instanceof PSQLException p && p.getServerErrorMessage() != null
            ? p.getServerErrorMessage().getConstraint() : null;
        return switch (erro.getSQLState()) {
            case "23505" -> new Resultado("conflito", 0, restricao); // UNIQUE
            case "23514" -> new Resultado("invalido", 0, restricao); // CHECK
            case "23503" -> new Resultado("referencia_inexistente", 0, restricao); // FOREIGN KEY
            default -> throw erro;
        };
    }

    static Resultado inserir(Connection db, String sql, Object... args) throws SQLException {
        try (var comando = db.prepareStatement(sql)) {
            for (int i = 0; i < args.length; i++) comando.setObject(i + 1, args[i]);
            try (var linhas = comando.executeQuery()) {
                linhas.next();
                return new Resultado("criado", linhas.getLong(1), null);
            }
        } catch (SQLException erro) {
            return traduzir(erro);
        }
    }

    public static void main(String[] args) throws Exception {
        try (var db = DriverManager.getConnection(System.getenv("JDBC_URL"))) {
            var cliente = "INSERT INTO clientes (email, nome) VALUES (?, ?) RETURNING id";
            var pedido = "INSERT INTO pedidos (cliente_id) VALUES (?) RETURNING id";
            System.out.println(inserir(db, cliente, "ana@exemplo.com", "Ana"));
            // mesmo e-mail, nome vazio e cliente inexistente
            System.out.println(inserir(db, cliente, "ANA@exemplo.com", "Ana Maria"));
            System.out.println(inserir(db, cliente, "bia@exemplo.com", "   "));
            System.out.println(inserir(db, pedido, 999_999L));
        }
    }
}

using Npgsql;

await using var db = NpgsqlDataSource.Create(Environment.GetEnvironmentVariable("DATABASE_URL")!);

// O banco garante as regras; a aplicação traduz a recusa numa resposta
Resultado Traduzir(PostgresException erro) => erro.SqlState switch
{
    PostgresErrorCodes.UniqueViolation => new("conflito", 0, erro.ConstraintName),
    PostgresErrorCodes.CheckViolation => new("invalido", 0, erro.ConstraintName),
    PostgresErrorCodes.ForeignKeyViolation => new("referencia_inexistente", 0, erro.ConstraintName),
    _ => throw erro,
};

async Task<Resultado> Inserir(string sql, params object[] args)
{
    await using var comando = db.CreateCommand(sql);
    foreach (var valor in args) comando.Parameters.Add(new NpgsqlParameter { Value = valor });
    try
    {
        var id = (long)(await comando.ExecuteScalarAsync())!;
        return new("criado", id, null);
    }
    catch (PostgresException erro)
    {
        return Traduzir(erro);
    }
}

const string Cliente = "INSERT INTO clientes (email, nome) VALUES ($1, $2) RETURNING id";
const string Pedido = "INSERT INTO pedidos (cliente_id) VALUES ($1) RETURNING id";
Console.WriteLine(await Inserir(Cliente, "ana@exemplo.com", "Ana"));
Console.WriteLine(await Inserir(Cliente, "ANA@exemplo.com", "Ana Maria")); // mesmo e-mail
Console.WriteLine(await Inserir(Cliente, "bia@exemplo.com", "   "));       // nome vazio
Console.WriteLine(await Inserir(Pedido, 999_999L));                        // cliente inexistente

record Resultado(string Tipo, long Id, string? Detalhe);

O resultado, nas cinco linguagens

Operação	Esperado	Nas cinco
Cadastrar `ana@exemplo.com`	criado, id 1	criado, id 1
Cadastrar `ANA@exemplo.com`	conflito em `clientes_email_unico`	conflito em `clientes_email_unico`
Cadastrar com nome em branco	inválido em `clientes_nome_check`	inválido em `clientes_nome_check`
Abrir pedido do cliente 999999	referência inexistente em `pedidos_cliente_id_fkey`	referência inexistente em `pedidos_cliente_id_fkey`

O nome da restrição é o que permite uma mensagem precisa: clientes_email_unico vira "este e-mail já está cadastrado". Dê nomes claros às restrições que a aplicação vai traduzir, como o índice clientes_email_unico do esquema. As outras receberam o nome automático do Postgres (clientes_nome_check, pedidos_cliente_id_fkey).

O que muda de uma linguagem para outra

	Driver	Como identificar a recusa	Nome da restrição
TypeScript	`pg`	`erro.code`, o `SQLSTATE`	`erro.constraint`
Python	`psycopg` 3	Classes como `errors.UniqueViolation`	`erro.diag.constraint_name`
Go	`pgx`	`errors.As` para `*pgconn.PgError` e `Code`	`ConstraintName`
Java	JDBC, driver `postgresql`	`getSQLState()`	`PSQLException.getServerErrorMessage().getConstraint()`
C#	`Npgsql`	`PostgresException.SqlState` e `PostgresErrorCodes`	`ConstraintName`

Lista de revisão

Estrutura

Cada tabela representa uma coisa, e cada fato fica num lugar só?
Os muitos para muitos têm uma tabela no meio, com a chave primária composta?
As cópias de dados (como o preço no item) têm motivo e fonte da verdade claros?

Chaves

A chave primária é bigint gerado, ou UUID v7 quando o identificador nasce fora do banco?
Toda chave estrangeira tem índice?
A ação de exclusão (RESTRICT, CASCADE, SET NULL) de cada chave estrangeira foi escolhida, e não deixada no padrão por acaso?

Regras

Campos obrigatórios têm NOT NULL?
A unicidade está no banco, inclusive sem diferenciar maiúsculas quando for o caso?
Listas fechadas e faixas de valores têm CHECK?
As restrições que a aplicação traduz têm nomes claros?

Tipos

Dinheiro em numeric ou centavos inteiros, nunca ponto flutuante?
Instantes em timestamptz, e datas sem hora em date?
Listas em tabelas filhas, e jsonb só para o que varia de fato?

O esquema da loja

Traduzir a recusa do banco, em cada linguagem

O resultado, nas cinco linguagens

O que muda de uma linguagem para outra

Lista de revisão

Prompts para aprofundar

Escrever o esquema e as migrações

Traduzir as recusas do banco na minha API

Revisar um modelo de dados