Conceptos básicos de modelado de datos

8 ejemplos para empezar con el modelado de datos: 5 básicos y 3 intermedios.

Prerrequisitos

• PostgreSQL 18.4 (o clúster 17.x compatible) con acceso a psql.
• Una base de datos temporal para experimentos de DDL: CREATE DATABASE modeling_lab;

Ejemplos básicos

1. Esquema conceptual de entidades

Nombra los sustantivos de tu dominio antes de escribir DDL.

-- Modelo conceptual (solo documentación - DDL no ejecutable)
-- Cliente --realiza--> Pedido --contiene--> ArtículoPedido
-- Producto --referenciado-por--> ArtículoPedido

• Empieza con entidades (Cliente, Pedido) y relaciones (realiza, contiene).
• La cardinalidad viene después; captura primero el lenguaje de negocio.
• Este esquema impulsa los nombres de tablas, la dirección de las FK y los límites de la API.
• Compártelo con producto y backend antes de que existan archivos de migración.

Relacionado: Modelado Entidad-Relación - cardinalidad y opcionalidad

2. Tabla lógica con clave sustituta

Mapea una entidad a una tabla con una clave primaria estable.

CREATE TABLE customers (
  customer_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email         text NOT NULL UNIQUE,
  display_name  text NOT NULL,
  created_at    timestamptz NOT NULL DEFAULT now()
);

• GENERATED ALWAYS AS IDENTITY es la clave sustituta nativa de PostgreSQL (preferida sobre serial).
• UNIQUE en email codifica una regla de negocio en la capa lógica.
• timestamptz almacena instantes absolutos; evita timestamp without time zone para horas visibles por el usuario.

Relacionado: Modelado Entidad-Relación - nombres y claves

3. Clave foránea para uno a muchos

Las filas hijas referencian a las filas padres; la columna FK vive en el lado "muchos".

CREATE TABLE orders (
  order_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  bigint NOT NULL REFERENCES customers (customer_id),
  status       text NOT NULL DEFAULT 'draft',
  placed_at    timestamptz
);
 
CREATE INDEX orders_customer_id_idx ON orders (customer_id);

• REFERENCES customers (customer_id) impone la integridad referencial en la base de datos.
• Indexa la columna FK (customer_id) para el rendimiento de join y cascade.
• El status por defecto documenta el punto de entrada del ciclo de vida en el modelo físico.

Relacionado: Modelado Entidad-Relación - patrones uno a muchos

4. Tabla de unión para muchos a muchos

Resuelve relaciones M:N con una tabla de enlace dedicada y unicidad compuesta.

CREATE TABLE products (
  product_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         text NOT NULL UNIQUE,
  name        text NOT NULL
);
 
CREATE TABLE order_items (
  order_id    bigint NOT NULL REFERENCES orders (order_id) ON DELETE CASCADE,
  product_id  bigint NOT NULL REFERENCES products (product_id),
  quantity    integer NOT NULL CHECK (quantity > 0),
  unit_price  numeric(12, 2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

• La PRIMARY KEY compuesta (order_id, product_id) evita duplicados de artículos por producto por pedido.
• ON DELETE CASCADE en order_id elimina los artículos del pedido cuando se elimina un pedido.
• Almacena unit_price en el momento del pedido (una instantánea), no solo el precio actual del catálogo.

Relacionado: Modelado Entidad-Relación - resolución M:N

5. Esquema como límite de contexto delimitado

Agrupa tablas relacionadas bajo un esquema de PostgreSQL para reflejar los límites del dominio.

CREATE SCHEMA billing;
CREATE SCHEMA catalog;
 
CREATE TABLE billing.invoices (
  invoice_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  bigint NOT NULL,
  issued_at    timestamptz NOT NULL DEFAULT now()
);
 
CREATE TABLE catalog.products (
  product_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku         text NOT NULL UNIQUE
);

• Los esquemas son espacios de nombres económicos: úsalos antes de dividir bases de datos.
• Las FK entre esquemas están permitidas pero señalan acoplamiento; documenta el contrato.
• search_path y los permisos por esquema controlan qué roles ven qué contexto.

Relacionado: Límites de Esquema Orientados al Dominio - contextos delimitados

Ejemplos intermedios

6. Modelo físico con restricciones CHECK

Codifica invariantes de los que la aplicación no debe ser la única guardiana.

CREATE TABLE orders (
  order_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  bigint NOT NULL REFERENCES customers (customer_id),
  status       text NOT NULL,
  placed_at    timestamptz,
  CONSTRAINT orders_status_check
    CHECK (status IN ('draft', 'placed', 'shipped', 'cancelled')),
  CONSTRAINT orders_placed_at_required
    CHECK (status = 'draft' OR placed_at IS NOT NULL)
);

• Las restricciones CHECK sobreviven a errores de aplicación y SQL ad-hoc.
• Empareja ENUM de estado con reglas temporales (placed_at requerido una vez colocado).
• Prefiere CHECK + text sobre ENUM de PostgreSQL cuando los valores cambian a menudo.

Relacionado: Estrategia de Evolución de Esquemas - evolución segura de restricciones

7. Vista de compatibilidad durante la evolución del esquema

Expone una interfaz estable mientras las columnas físicas se mueven detrás de las migraciones.

ALTER TABLE customers ADD COLUMN legal_name text;
 
UPDATE customers SET legal_name = display_name WHERE legal_name IS NULL;
 
CREATE OR REPLACE VIEW customers_v1 AS
SELECT
  customer_id,
  email,
  display_name,
  COALESCE(legal_name, display_name) AS name_for_invoices,
  created_at
FROM customers;

• Las vistas permiten a los servicios leer un contrato estable mientras las columnas se renombran o dividen.
• CREATE OR REPLACE VIEW es un paso de expansión de bajo riesgo en migraciones de expansión-contracción.
• Deprecia la vista solo después de que todos los consumidores se muevan a los nuevos nombres de columna.

Relacionado: Estrategia de Evolución de Esquemas - vistas compatibles con versiones anteriores

8. Comentario de propiedad estilo ADR

Documenta quién es el propietario de una tabla y si otros servicios pueden escribir en ella.

COMMENT ON TABLE billing.invoices IS
  'Owner: billing-service. Writes: billing-service only. '
  'Reads: reporting-replica, finance-batch. '
  'ADR: shared-db-with-schema-isolation (2024-06).';

• La metadato COMMENT ON sobrevive en volcados y en la salida de \d+.
• Vincula comentarios a un ADR al debatir bases de datos únicas frente a bases de datos por servicio.
• Los comentarios de propiedad evitan escrituras "útiles" entre servicios que corrompen invariantes.

Relacionado: ADR: Base de Datos Única vs. Base de Datos por Servicio - compensaciones de propiedad de datos

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+.