Conceptos básicos de diseño de esquemas

9 ejemplos para empezar con el diseño de esquemas: 6 básicos y 3 intermedios.

Prerrequisitos

CREATE en una base de datos de desarrollo.

# Desarrollo local con Docker (PostgreSQL 18)
docker run -d --name pg18 -e POSTGRES_PASSWORD=dev -p 5432:5432 postgres:18
psql postgres://postgres:dev@localhost:5432/postgres

Ejemplos básicos

1. Clave primaria sustituta

ID opaco generado por la base de datos.

CREATE TABLE app.accounts (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

• Estable cuando las claves naturales del negocio cambian
• Referencias FK estrechas
• Expone el ID en las API de forma consistente

2. Clave única natural

Identificador de negocio con restricción UNIQUE.

CREATE TABLE app.accounts (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  external_ref text NOT NULL UNIQUE,
  name text NOT NULL
);

• UNIQUE impone la identidad de los sistemas externos
• Conserva la PK sustituta para las uniones
• Documenta la fuente de external_ref

3. Nomenclatura de tablas

Sustantivos plurales y prefijo de esquema.

CREATE TABLE app.order_lines (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id bigint NOT NULL,
  sku text NOT NULL,
  qty int NOT NULL CHECK (qty > 0)
);

• Plural o singular consistente según el estándar de la organización
• Prefija las columnas con el contexto de la tabla al unir selecciones amplias
• Evita palabras reservadas

4. Columna de clave foránea

Nombra la columna hija parent_table_id.

ALTER TABLE app.order_lines
  ADD CONSTRAINT order_lines_order_id_fkey
  FOREIGN KEY (order_id) REFERENCES app.orders(id);

• El comportamiento explícito de ON DELETE es necesario en el diseño
• Indexa las columnas FK en las tablas hijas
• NOT NULL cuando la relación es obligatoria

5. Valores predeterminados NOT NULL

Reduce la ambigüedad de NULL.

CREATE TABLE app.settings (
  account_id bigint PRIMARY KEY REFERENCES app.accounts(id),
  theme text NOT NULL DEFAULT 'light'
);

• Los valores predeterminados aceleran las inserciones
• NOT NULL cuando lo desconocido es inválido
• NULL reservado para desconocidos opcionales

6. Restricciones CHECK

Reglas de fila aplicadas por la base de datos.

ALTER TABLE app.order_lines
  ADD CONSTRAINT order_lines_qty_positive CHECK (qty > 0);

• Sobreviven a errores de aplicación
• Las restricciones con nombre mejoran los mensajes de error
• Documenta en comentarios de migración

Ejemplos intermedios

7. Clave natural compuesta

Cuando el par de negocio es único.

CREATE TABLE app.region_codes (
  country char(2) NOT NULL,
  region_code text NOT NULL,
  name text NOT NULL,
  PRIMARY KEY (country, region_code)
);

• Bueno para datos de referencia puros
• FKs más amplias en tablas hijas
• Considera una sustituta si el ancho de la FK es importante

8. Columna de borrado lógico

deleted_at marca las filas inactivas.

ALTER TABLE app.accounts ADD COLUMN deleted_at timestamptz;
CREATE INDEX accounts_active_idx ON app.accounts (id) WHERE deleted_at IS NULL;

• El índice parcial mantiene pequeño el conjunto "caliente"
• Las restricciones únicas pueden necesitar un índice único parcial
• Vacuum todavía procesa tuplas muertas

9. Comentarios sobre objetos

Documentación del catálogo.

COMMENT ON TABLE app.accounts IS 'Tenant billing account';
COMMENT ON COLUMN app.accounts.external_ref IS 'CRM id';

• Visibles en psql \d+ y catálogos de datos
• Sobreviven a la regeneración del ORM si las migraciones poseen DDL
• Ayuda a la incorporación

Versiones de la pila: Esta página se escribió para PostgreSQL 18.4 (estable 18, mantenimiento 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x y PostGIS 3.5+.