Design relacional: chaves e relações

De entidades a tabelas

Projetar um banco de dados relacional consiste em identificar as entidades do domínio (clientes, pedidos, produtos...) e converter cada uma em uma tabela, decidindo suas colunas, seus tipos de dados e como elas se relacionam.

Chave primária (PK)

A chave primária (primary key) identifica de forma única cada linha de uma tabela. Não pode se repetir nem ser nula.

CREATE TABLE clientes (
  id INTEGER PRIMARY KEY,   -- identifica cada cliente sem ambiguidade
  nome TEXT NOT NULL,
  email TEXT UNIQUE
);

Uma boa PK é estável e sem significado de negócio (um id numérico costuma ser melhor que o email ou o CPF, que podem mudar).

Chave estrangeira (FK)

Uma chave estrangeira (foreign key) é uma coluna que aponta para a chave primária de outra tabela. Serve para conectar tabelas e para que o banco de dados garanta a integridade referencial: você não conseguirá criar um pedido de um cliente que não existe.

CREATE TABLE pedidos (
  id INTEGER PRIMARY KEY,
  cliente_id INTEGER NOT NULL,
  total REAL NOT NULL,
  FOREIGN KEY (cliente_id) REFERENCES clientes(id)
);

Tipos de dados

Cada coluna tem um tipo que define quais valores admite. No SQLite os tipos mais usados são:

Escolher bem o tipo evita erros e melhora o desempenho e a validação.

Tipos de relação

1:1 (um para um)

Cada linha de A corresponde a no máximo uma linha de B e vice-versa. Por exemplo, usuario e perfil_estendido. Modela-se colocando uma FK com restrição UNIQUE em uma das tabelas.

1:N (um para muitos)

A relação mais comum: uma linha de A se relaciona com muitas de B, mas cada linha de B com uma só de A. Um cliente tem muitos pedidos; cada pedido pertence a um cliente. Modela-se com uma FK no lado "muitos" (pedidos.cliente_id).

N:M (muitos para muitos)

Muitas linhas de A se relacionam com muitas de B. Por exemplo, pedidos e produtos: um pedido contém vários produtos e um produto aparece em vários pedidos. Não se pode representar com uma única FK: é necessária uma tabela intermediária (também chamada de tabela ponte ou de junção) que tem uma FK para cada lado.

CREATE TABLE linha_pedido (
  pedido_id INTEGER,
  produto_id INTEGER,
  quantidade INTEGER NOT NULL,
  PRIMARY KEY (pedido_id, produto_id),       -- chave composta
  FOREIGN KEY (pedido_id) REFERENCES pedidos(id),
  FOREIGN KEY (produto_id) REFERENCES produtos(id)
);

Regra prática: toda relação N:M se resolve com uma tabela intermediária que contém as duas FK (e muitas vezes dados próprios da relação, como a quantidade).