SQL Schema Modelação
SQL

Modelação ER & Tipos de Dados

Antes de escrever uma única linha de SQL, é necessário modelar o domínio — identificar as entidades que existem no sistema, os atributos que as descrevem, e as relações entre elas. O modelo Entidade-Relação (ER) é a linguagem visual que formaliza esse pensamento. A tradução do modelo ER para tabelas SQL é um processo sistemático, e a escolha dos tipos de dados correctos para cada coluna é a primeira decisão de performance e integridade que tomamos num schema.

Entidades, Atributos e Relações

Uma entidade é um objecto do mundo real com existência independente — um cliente, uma encomenda, um produto. Um atributo é uma propriedade dessa entidade — o nome do cliente, a data da encomenda, o preço do produto. Uma relação é uma associação semântica entre duas ou mais entidades — um cliente faz encomendas, uma encomenda contém produtos. 

-- Domínio de exemplo: sistema de encomendas de uma loja online
--
-- Entidades:
--   Cliente   — representa uma pessoa que pode fazer encomendas
--   Encomenda — representa uma compra feita por um cliente
--   Produto   — representa um artigo disponível para venda
--   Categoria — representa um grupo temático de produtos
--
-- Relações:
--   Cliente  ──< Encomenda     (1 cliente pode ter N encomendas)
--   Encomenda >──< Produto     (N encomendas podem conter N produtos — relação M:N)
--   Produto  >── Categoria     (N produtos pertencem a 1 categoria)
--
-- Notação crow's foot (usada em diagramas ER):
--   |o── zero ou um
--   ||── exactamente um (obrigatório)
--   |<── um ou muitos
--   o<── zero ou muitos
--
-- Exemplo de diagrama textual:
--
--   CLIENTE ||──o< ENCOMENDA >o──|| LINHA_ENCOMENDA ||──o< PRODUTO >o──|| CATEGORIA
--
--   Leitura:
--   ─ Um CLIENTE tem zero ou mais ENCOMENDAS
--   ─ Uma ENCOMENDA pertence a exactamente um CLIENTE
--   ─ Uma ENCOMENDA tem uma ou mais LINHAS_ENCOMENDA
--   ─ Um PRODUTO pode aparecer em zero ou mais LINHAS_ENCOMENDA
--   ─ Um PRODUTO pertence a exactamente uma CATEGORIA

Cardinalidade e Obrigatoriedade

A cardinalidade descreve quantos de um lado da relação podem existir para cada instância do outro lado. A obrigatoriedade (ou participação) descreve se a relação é obrigatória ou opcional. As duas dimensões combinadas determinam as constraints do schema. 

-- Tipos de relação por cardinalidade:

-- ── 1:1 — Um para Um ─────────────────────────────────────────────────────────
-- Cada instância de A está associada a no máximo uma instância de B e vice-versa
-- Exemplo: Utilizador ──── Perfil (cada utilizador tem exactamente um perfil)
-- Implementação: chave estrangeira em qualquer uma das tabelas, com UNIQUE constraint

CREATE TABLE utilizador (
    id   SERIAL PRIMARY KEY,
    nome VARCHAR(100) NOT NULL
);

CREATE TABLE perfil (
    id             SERIAL PRIMARY KEY,
    utilizador_id  INT NOT NULL UNIQUE,        -- UNIQUE → garante relação 1:1
    bio            TEXT,
    avatar_url     VARCHAR(500),
    CONSTRAINT fk_perfil_utilizador
        FOREIGN KEY (utilizador_id) REFERENCES utilizador(id)
        ON DELETE CASCADE
);


-- ── 1:N — Um para Muitos ──────────────────────────────────────────────────────
-- Cada instância de A pode estar associada a N instâncias de B
-- Mas cada instância de B está associada a no máximo 1 instância de A
-- Exemplo: Cliente ──< Encomenda (um cliente tem várias encomendas)
-- Implementação: chave estrangeira no lado N (na tabela filha)

CREATE TABLE cliente (
    id    SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    nome  VARCHAR(100) NOT NULL
);

CREATE TABLE encomenda (
    id          SERIAL PRIMARY KEY,
    cliente_id  INT NOT NULL,              -- lado N — chave estrangeira
    data        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    total       NUMERIC(10, 2) NOT NULL,
    CONSTRAINT fk_encomenda_cliente
        FOREIGN KEY (cliente_id) REFERENCES cliente(id)
        ON DELETE RESTRICT                 -- não apagar clientes com encomendas
);


-- ── M:N — Muitos para Muitos ──────────────────────────────────────────────────
-- Cada instância de A pode estar associada a N instâncias de B e vice-versa
-- Exemplo: Encomenda >──< Produto (uma encomenda tem vários produtos,
--          um produto aparece em várias encomendas)
-- Implementação: tabela de junção (junction table / associative entity)
-- A tabela de junção tem SEMPRE atributos próprios (quantidade, preço aplicado, etc.)
-- Se não tiver atributos, é sinal de que o modelo pode estar incompleto

CREATE TABLE produto (
    id         SERIAL PRIMARY KEY,
    nome       VARCHAR(200) NOT NULL,
    preco_base NUMERIC(10, 2) NOT NULL,
    stock      INT NOT NULL DEFAULT 0
);

CREATE TABLE linha_encomenda (        -- tabela de junção M:N
    encomenda_id  INT NOT NULL,
    produto_id    INT NOT NULL,
    quantidade    INT NOT NULL DEFAULT 1,
    preco_unit    NUMERIC(10, 2) NOT NULL,  -- preço no momento da compra (pode mudar depois)
    PRIMARY KEY (encomenda_id, produto_id), -- chave composta — cada par é único
    CONSTRAINT fk_linha_encomenda
        FOREIGN KEY (encomenda_id) REFERENCES encomenda(id) ON DELETE CASCADE,
    CONSTRAINT fk_linha_produto
        FOREIGN KEY (produto_id) REFERENCES produto(id) ON DELETE RESTRICT
);

Chaves Primárias e Estrangeiras

-- ── Chave Primária (PRIMARY KEY) ─────────────────────────────────────────────
-- Identifica univocamente cada linha de uma tabela
-- Implicitamente: NOT NULL + UNIQUE + índice B-tree

-- Opção 1: SERIAL / BIGSERIAL — inteiro auto-incrementado (simples, popular)
CREATE TABLE categoria (
    id   SERIAL PRIMARY KEY,          -- SERIAL = INTEGER com sequência automática
    nome VARCHAR(100) NOT NULL UNIQUE
);
-- SERIAL é um atalho para:
-- id INTEGER NOT NULL DEFAULT nextval('categoria_id_seq')

-- Opção 2: BIGSERIAL — para tabelas com muitas linhas (> 2 milhões)
CREATE TABLE evento_log (
    id         BIGSERIAL PRIMARY KEY, -- 64-bit: até 9.2 × 10^18 linhas
    timestamp  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    mensagem   TEXT
);

-- Opção 3: UUID — identificador universal único
-- Vantagens: sem colisões em sistemas distribuídos, opaco (não revela contagem)
-- Desvantagens: 16 bytes vs 4-8, índices ligeiramente maiores
CREATE EXTENSION IF NOT EXISTS "pgcrypto";  -- PostgreSQL
CREATE TABLE sessao (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    utilizador_id INT NOT NULL,
    criada_em   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Chave primária composta — usada em tabelas de junção:
CREATE TABLE permissao_role (
    utilizador_id  INT NOT NULL,
    role_id        INT NOT NULL,
    concedida_em   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (utilizador_id, role_id)  -- combinação única, nenhum campo é único sozinho
);


-- ── Chave Estrangeira (FOREIGN KEY) ──────────────────────────────────────────
-- Garante integridade referencial — não podes referenciar uma linha que não existe

-- ON DELETE / ON UPDATE comportamentos:
-- RESTRICT   — impede a remoção/alteração se existirem linhas dependentes (mais seguro)
-- CASCADE    — propaga a remoção/alteração automaticamente para as linhas dependentes
-- SET NULL   — define a chave estrangeira como NULL quando o pai é removido
-- SET DEFAULT — define o valor default quando o pai é removido
-- NO ACTION  — como RESTRICT mas verificado no final da transacção (permite deferral)

CREATE TABLE comentario (
    id          SERIAL PRIMARY KEY,
    post_id     INT,              -- pode ser NULL → comentário pode existir sem post (raro)
    autor_id    INT NOT NULL,
    texto       TEXT NOT NULL,
    CONSTRAINT fk_comentario_post
        FOREIGN KEY (post_id) REFERENCES post(id)
        ON DELETE SET NULL,       -- se o post for apagado, comentários ficam sem post
    CONSTRAINT fk_comentario_autor
        FOREIGN KEY (autor_id) REFERENCES utilizador(id)
        ON DELETE RESTRICT        -- não apagar utilizadores com comentários
);

Tipos de Dados SQL

A escolha do tipo de dados correcto para cada coluna é uma decisão de integridade e performance. Um tipo demasiado largo desperdiça espaço em disco e memória (relevante para índices); um tipo demasiado estreito causa erros de truncamento ou overflow. Os exemplos abaixo usam sintaxe PostgreSQL — os tipos SQL standard são portáveis entre a maioria dos SGBD. 

-- ── Tipos Numéricos ──────────────────────────────────────────────────────────

-- Inteiros:
SMALLINT          -- 2 bytes, -32768 a 32767          — raramente necessário
INTEGER (INT)     -- 4 bytes, -2.1B a 2.1B            — chave primária, contadores
BIGINT            -- 8 bytes, -9.2×10^18 a 9.2×10^18 — IDs em sistemas de grande escala

-- Decimais exactos (financeiro):
NUMERIC(p, s)     -- p = total de dígitos, s = casas decimais
DECIMAL(p, s)     -- sinónimo de NUMERIC no PostgreSQL
-- Exemplo: NUMERIC(10, 2) → até 99 999 999.99 — ideal para preços e valores monetários
-- NUNCA usar FLOAT/REAL para valores financeiros — representação binária causa erros:
-- 0.1 + 0.2 = 0.30000000000000004 em ponto flutuante

-- Ponto flutuante (cálculo científico, coordenadas aproximadas):
REAL              -- 4 bytes, ~6 dígitos de precisão
DOUBLE PRECISION  -- 8 bytes, ~15 dígitos de precisão


-- ── Tipos de Texto ────────────────────────────────────────────────────────────

VARCHAR(n)        -- texto de comprimento variável, máximo n caracteres
CHAR(n)           -- texto de comprimento fixo, preenchido com espaços — evitar em geral
TEXT              -- texto de comprimento ilimitado — sem overhead vs VARCHAR(n) no PostgreSQL

-- Guideline de escolha:
-- VARCHAR(n)  quando existe um limite semântico real (email: 255, nome: 100)
-- TEXT        para conteúdo livre (descrições, comentários, posts)
-- CHAR(n)     apenas para códigos de comprimento fixo garantido (ISO country: CHAR(2))

-- Exemplo:
CREATE TABLE utilizador (
    id        SERIAL PRIMARY KEY,
    email     VARCHAR(255)  NOT NULL UNIQUE, -- limite real: spec RFC 5321 = 254 chars
    nome      VARCHAR(100)  NOT NULL,
    bio       TEXT,                          -- sem limite — conteúdo livre
    pais      CHAR(2)                        -- ISO 3166-1 alpha-2: 'PT', 'US', 'GB'
);


-- ── Tipos de Data e Hora ──────────────────────────────────────────────────────

DATE              -- apenas data: '2024-01-15'
TIME              -- apenas hora: '14:30:00'
TIMESTAMP         -- data + hora sem timezone: '2024-01-15 14:30:00'
TIMESTAMPTZ       -- data + hora COM timezone (recomendado para aplicações distribuídas)
INTERVAL          -- duração: '3 days', '2 hours 30 minutes'

-- Regra de ouro: SEMPRE usar TIMESTAMPTZ em vez de TIMESTAMP
-- TIMESTAMP armazena a hora "como está" — sem contexto de timezone
-- TIMESTAMPTZ converte para UTC internamente, converte de volta ao ler
-- Resultado: dois servidores em timezones diferentes vêem sempre o mesmo momento

CREATE TABLE encomenda (
    id           SERIAL PRIMARY KEY,
    criada_em    TIMESTAMPTZ NOT NULL DEFAULT NOW(),  -- momento exacto da criação
    data_entrega DATE,                                -- só a data importa (sem hora)
    tempo_prep   INTERVAL                             -- duração do processamento
);

-- Funções úteis:
SELECT NOW();                               -- timestamp actual com timezone
SELECT CURRENT_DATE;                        -- data actual
SELECT AGE(NOW(), criada_em) FROM encomenda; -- intervalo desde a criação
SELECT DATE_TRUNC('month', criada_em) FROM encomenda; -- truncar para início do mês


-- ── Tipos Booleano e Binário ──────────────────────────────────────────────────

BOOLEAN           -- TRUE / FALSE / NULL
BYTEA             -- dados binários em bruto (imagens, ficheiros pequenos)
                  -- para ficheiros grandes: armazenar path, guardar no sistema de ficheiros

-- Nota: NULL em BOOLEAN é "desconhecido" — diferente de FALSE
-- WHERE activo = TRUE   → exclui NULL e FALSE
-- WHERE activo IS TRUE  → exclui NULL e FALSE (semanticamente mais correcto)
-- WHERE activo IS NOT FALSE → inclui TRUE e NULL


-- ── Tipos Especiais PostgreSQL ────────────────────────────────────────────────

UUID              -- identificador universal único: '550e8400-e29b-41d4-a716-446655440000'
JSON              -- texto JSON sem validação de estrutura
JSONB             -- JSON binário — indexável, mais rápido para queries, maior espaço
ARRAY             -- array de qualquer tipo: INTEGER[], TEXT[], etc.

-- JSONB vs colunas normais:
-- JSONB: útil para atributos semi-estruturados ou que variam por registo
-- Colunas: preferível quando o campo é consultado frequentemente ou precisa de índice simples
-- Regra: se fizeres WHERE sobre um campo dentro do JSON frequentemente → coluna normal

CREATE TABLE produto (
    id          SERIAL PRIMARY KEY,
    nome        VARCHAR(200) NOT NULL,
    preco       NUMERIC(10, 2) NOT NULL,
    tags        TEXT[],                  -- array de tags: '{"electrónica", "portátil"}'
    atributos   JSONB                    -- especificações variáveis por categoria de produto
);

-- Aceder a JSONB:
SELECT atributos->>'cor' FROM produto WHERE id = 1;        -- valor como texto
SELECT atributos->'dimensoes'->>'largura' FROM produto;    -- valor aninhado
SELECT * FROM produto WHERE atributos @> '{"cor": "azul"}'; -- contém JSON (usa índice GIN)

Constraints de Integridade

-- Constraints garantem a integridade dos dados ao nível do schema
-- São verificadas em cada INSERT, UPDATE e DELETE — erros são lançados como excepções

CREATE TABLE produto (
    id           SERIAL PRIMARY KEY,
    nome         VARCHAR(200)   NOT NULL,         -- NOT NULL: valor obrigatório
    preco        NUMERIC(10, 2) NOT NULL,
    stock        INT            NOT NULL DEFAULT 0,
    sku          VARCHAR(50)    NOT NULL UNIQUE,   -- UNIQUE: sem duplicados
    categoria_id INT            NOT NULL,

    -- CHECK: condição que o valor deve satisfazer
    CONSTRAINT chk_preco_positivo  CHECK (preco > 0),
    CONSTRAINT chk_stock_nao_neg   CHECK (stock >= 0),
    CONSTRAINT chk_nome_len        CHECK (char_length(nome) >= 2),

    CONSTRAINT fk_produto_categoria
        FOREIGN KEY (categoria_id) REFERENCES categoria(id)
        ON DELETE RESTRICT
);

-- Constraint entre múltiplas colunas:
CREATE TABLE horario (
    id          SERIAL PRIMARY KEY,
    sala_id     INT  NOT NULL,
    inicio      TIMESTAMPTZ NOT NULL,
    fim         TIMESTAMPTZ NOT NULL,
    CONSTRAINT chk_horario_valido CHECK (fim > inicio),       -- fim deve ser depois de início
    CONSTRAINT uq_sala_horario    UNIQUE (sala_id, inicio)    -- sem sobreposição de início
);

-- Adicionar constraint a tabela existente:
ALTER TABLE produto
    ADD CONSTRAINT chk_sku_formato CHECK (sku ~ '^[A-Z0-9\-]+$');
    -- ~ é regex no PostgreSQL: SKU só pode ter maiúsculas, dígitos e hífens

-- Ver todas as constraints de uma tabela:
SELECT conname, contype, pg_get_constraintdef(oid)
FROM   pg_constraint
WHERE  conrelid = 'produto'::regclass;

Schema Completo do Exemplo

-- Schema completo do sistema de encomendas — pronto para executar no PostgreSQL

CREATE TABLE categoria (
    id   SERIAL PRIMARY KEY,
    nome VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE produto (
    id           SERIAL PRIMARY KEY,
    nome         VARCHAR(200)   NOT NULL,
    descricao    TEXT,
    preco_base   NUMERIC(10, 2) NOT NULL,
    stock        INT            NOT NULL DEFAULT 0,
    sku          VARCHAR(50)    NOT NULL UNIQUE,
    categoria_id INT            NOT NULL,
    criado_em    TIMESTAMPTZ    NOT NULL DEFAULT NOW(),
    CONSTRAINT chk_preco_positivo CHECK (preco_base > 0),
    CONSTRAINT chk_stock_nao_neg  CHECK (stock >= 0),
    CONSTRAINT fk_produto_cat
        FOREIGN KEY (categoria_id) REFERENCES categoria(id) ON DELETE RESTRICT
);

CREATE TABLE cliente (
    id        SERIAL PRIMARY KEY,
    email     VARCHAR(255) NOT NULL UNIQUE,
    nome      VARCHAR(100) NOT NULL,
    pais      CHAR(2)      NOT NULL DEFAULT 'PT',
    criado_em TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE TABLE encomenda (
    id          SERIAL PRIMARY KEY,
    cliente_id  INT            NOT NULL,
    estado      VARCHAR(20)    NOT NULL DEFAULT 'pendente',
    total       NUMERIC(10, 2) NOT NULL DEFAULT 0,
    criada_em   TIMESTAMPTZ    NOT NULL DEFAULT NOW(),
    entregue_em TIMESTAMPTZ,
    CONSTRAINT chk_estado
        CHECK (estado IN ('pendente', 'pago', 'enviado', 'entregue', 'cancelado')),
    CONSTRAINT fk_encomenda_cliente
        FOREIGN KEY (cliente_id) REFERENCES cliente(id) ON DELETE RESTRICT
);

CREATE TABLE linha_encomenda (
    encomenda_id INT            NOT NULL,
    produto_id   INT            NOT NULL,
    quantidade   INT            NOT NULL DEFAULT 1,
    preco_unit   NUMERIC(10, 2) NOT NULL,
    PRIMARY KEY (encomenda_id, produto_id),
    CONSTRAINT chk_qtd_positiva CHECK (quantidade > 0),
    CONSTRAINT fk_linha_enc
        FOREIGN KEY (encomenda_id) REFERENCES encomenda(id) ON DELETE CASCADE,
    CONSTRAINT fk_linha_prod
        FOREIGN KEY (produto_id) REFERENCES produto(id) ON DELETE RESTRICT
);

-- Inserir dados de teste:
INSERT INTO categoria (nome) VALUES ('Electrónica'), ('Vestuário'), ('Livros');

INSERT INTO produto (nome, preco_base, stock, sku, categoria_id)
VALUES
    ('Teclado Mecânico', 89.99,  50, 'TEC-001', 1),
    ('T-shirt Algodão',  19.99, 200, 'VES-001', 2),
    ('Clean Code',       34.90,  30, 'LIV-001', 3);

INSERT INTO cliente (email, nome) VALUES ('joao@example.com', 'João Silva');

INSERT INTO encomenda (cliente_id, total) VALUES (1, 124.89);

INSERT INTO linha_encomenda (encomenda_id, produto_id, quantidade, preco_unit)
VALUES (1, 1, 1, 89.99), (1, 3, 1, 34.90);

Checklist Modelação ER & Tipos de Dados

Início