Guía de Diseño de Bases de Datos

Resumen

Una base de datos bien diseñada es la fundación de aplicaciones confiables. Un mal diseño conduce a inconsistencias de datos, consultas lentas y migraciones costosas. A continuación: los principios esenciales para diseñar bases de datos relacionales escalables.

Modelado Entidad-Relación

Comienza cada diseño identificando entidades y relaciones.

Pasos

1. Identificar entidades: Usuarios, Órdenes, Productos, Categorías
2. Definir atributos: ¿Qué datos tiene cada entidad?
3. Mapear relaciones: Uno-a-uno, uno-a-muchos, muchos-a-muchos
4. Asignar claves: Claves primarias, candidatas, compuestas

Tipos de Relaciones

Tipo	Ejemplo	Implementación
Uno-a-Uno	Usuario → Perfil	Clave foránea con constraint único
Uno-a-Muchos	Categoría → Productos	Clave foránea en el lado “muchos”
Muchos-a-Muchos	Estudiantes ↔ Cursos	Tabla intermedia con dos claves foráneas

Normalización

La normalización reduce la redundancia y previene anomalías.

Primera Forma Normal (1NF)

• Valores atómicos: sin atributos multivaluados
• Cada fila es única (tiene clave primaria)

Segunda Forma Normal (2NF)

• Debe estar en 1NF
• Sin dependencia parcial: atributos no clave dependen de la clave primaria completa

Tercera Forma Normal (3NF)

• Debe estar en 2NF
• Sin dependencia transitiva: atributos no clave dependen solo de la clave primaria

Ejemplo de Esquema Normalizado

CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  email      VARCHAR(255) NOT NULL UNIQUE,
  name       VARCHAR(100) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  user_id     INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total       DECIMAL(10,2) NOT NULL,
  status      VARCHAR(20) NOT NULL DEFAULT 'pending',
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status) WHERE status != 'archived';

Estrategias de Indexación

Los índices aceleran lecturas pero ralentizan escrituras.

Cuándo Indexar

Escenario	Tipo de Índice
Búsquedas primarias	B-Tree en clave primaria
Columnas de clave foránea	B-Tree en columnas FK
Búsqueda full-text	Índice full-text
Consultas por rango	B-Tree en la columna
Datos geoespaciales	GiST / SP-GiST

Errores Comunes

• Indexación excesiva: cada índice ralentiza INSERT/UPDATE/DELETE
• Indexar columnas de baja cardinalidad solas
• Ignorar índices de cobertura para consultas frecuentes

Constraints e Integridad de Datos

Constraint	Propósito
PRIMARY KEY	Identificador único para cada fila
UNIQUE	Asegura valores sin duplicados
NOT NULL	Previene valores faltantes
CHECK	Valida datos con expresiones
FOREIGN KEY	Mantiene integridad referencial

Lo que funciona

• Usar claves sustitutas (auto-increment o UUIDs) en vez de claves naturales
• Evitar claves foráneas nulas: usa tablas de intersección para relaciones opcionales
• Elegir tipos de datos cuidadosamente: VARCHAR(255) vs TEXT, DECIMAL vs FLOAT
• Documentar el schema: con comentarios y diagramas ER
• Planificar el crecimiento: particionar tablas grandes antes de que se conviertan en un problema

Errores Comunes

• Saltarse la normalización por “performance” sin evidencia. Consulta tuning SQL.
• Usar ENUM para valores que cambian frecuentemente
• Faltar reglas ON DELETE / ON UPDATE en claves foráneas. Consulta validación de datos.
• Almacenar datos derivados/calculados en vez de computar en lectura

Notas de Producción

• Despliega gradualmente usando canary o blue-green para detectar regresiones temprano.
• Configura alertas para errores, latencia p99 y tasa de fallos antes de habilitar en producción.
• Documenta el rollback en el runbook; prueba el procedimiento en staging al menos una vez por trimestre.
• Revisa logs estructurados con correlation IDs para trazar requests end-to-end en incidentes.

Puntos Clave

• Aplica guía de diseño de bases de datos cuando necesites una solución práctica para tu caso de uso.
• Monitorea el rendimiento después de implementar; mide latencia, errores y uso de recursos antes y después.
• Revisa la sección de Troubleshooting ante errores comunes; la mayoría tienen causa raíz documentada con solución.
• Mantén dependencias actualizadas y ejecuta tests en CI para prevenir regresiones en producción.

Temas Avanzados

Escenario Detallado: Diseno de Base de Datos para E-commerce

Sistema: Plataforma e-commerce (PostgreSQL 16)
Volumen: 500K productos, 2M usuarios, 10M ordenes
Requisitos: Busqueda full-text, inventario multi-almacen, auditoria

Esquema principal:

  -- Tabla de usuarios con particion por region
  CREATE TABLE users (
      id BIGSERIAL PRIMARY KEY,
      email VARCHAR(255) NOT NULL UNIQUE,
      name VARCHAR(100) NOT NULL,
      region VARCHAR(10) NOT NULL,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
  ) PARTITION BY LIST (region);

  CREATE TABLE users_us PARTITION OF users FOR VALUES IN ("US");
  CREATE TABLE users_eu PARTITION OF users FOR VALUES IN ("EU");
  CREATE TABLE users_latam PARTITION OF users FOR VALUES IN ("LATAM");

  -- Productos con busqueda full-text
  CREATE TABLE products (
      id BIGSERIAL PRIMARY KEY,
      name VARCHAR(200) NOT NULL,
      description TEXT,
      price DECIMAL(10,2) NOT NULL,
      category_id INT NOT NULL REFERENCES categories(id),
      sku VARCHAR(50) NOT NULL UNIQUE,
      search_vector TSVECTOR GENERATED ALWAYS AS
          (to_tsvector("english", name || " " || coalesce(description, ""))) STORED
  );

  CREATE INDEX idx_products_search ON products USING GIN(search_vector);
  CREATE INDEX idx_products_category ON products(category_id);
  CREATE INDEX idx_products_price ON products(price);

  -- Inventario multi-almacen
  CREATE TABLE inventory (
      product_id BIGINT NOT NULL REFERENCES products(id),
      warehouse_id INT NOT NULL REFERENCES warehouses(id),
      quantity INT NOT NULL DEFAULT 0,
      reserved INT NOT NULL DEFAULT 0,
      CHECK (quantity >= 0),
      CHECK (reserved >= 0),
      CHECK (reserved <= quantity),
      PRIMARY KEY (product_id, warehouse_id)
  );

  CREATE INDEX idx_inventory_warehouse ON inventory(warehouse_id, quantity);

  -- Ordenes con auditoria
  CREATE TABLE orders (
      id BIGSERIAL PRIMARY KEY,
      user_id BIGINT NOT NULL REFERENCES users(id),
      status VARCHAR(20) NOT NULL DEFAULT "pending",
      total DECIMAL(12,2) NOT NULL,
      created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
  ) PARTITION BY RANGE (created_at);

  CREATE TABLE orders_2026_q1 PARTITION OF orders
      FOR VALUES FROM ("2026-01-01") TO ("2026-04-01");
  CREATE TABLE orders_2026_q2 PARTITION OF orders
      FOR VALUES FROM ("2026-04-01") TO ("2026-07-01");

  CREATE INDEX idx_orders_user ON orders(user_id, created_at DESC);
  CREATE INDEX idx_orders_status ON orders(status) WHERE status != "completed";

  -- Tabla de auditoria con triggers
  CREATE TABLE audit_log (
      id BIGSERIAL PRIMARY KEY,
      table_name VARCHAR(50) NOT NULL,
      record_id BIGINT NOT NULL,
      action VARCHAR(10) NOT NULL,
      old_data JSONB,
      new_data JSONB,
      changed_by VARCHAR(100),
      changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
  );

  CREATE OR REPLACE FUNCTION audit_order_changes()
  RETURNS TRIGGER AS $$
  BEGIN
      INSERT INTO audit_log (table_name, record_id, action, old_data, new_data, changed_by)
      VALUES ("orders", COALESCE(NEW.id, OLD.id), TG_OP,
              CASE WHEN TG_OP = "DELETE" THEN to_jsonb(OLD) END,
              CASE WHEN TG_OP != "DELETE" THEN to_jsonb(NEW) END,
              current_user);
      RETURN COALESCE(NEW, OLD);
  END;
  $$ LANGUAGE plpgsql;

  CREATE TRIGGER orders_audit
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH ROW EXECUTE FUNCTION audit_order_changes();

Decisiones de diseno:
  - Particion por region para usuarios (queries regionales frecuentes)
  - Particion por fecha para ordenes (archivar ordenes viejas)
  - TSVector para busqueda full-text sin Elasticsearch
  - Inventario separado de productos (multi-almacen)
  - Audit log con triggers para cumplimiento
  - CHECK constraints para invariantes de negocio

Lecciones aprendidas:
  - Particionar temprano en tablas que creceran mucho
  - Los indices parciales (WHERE status != "completed") ahorran espacio
  - TSVector elimina la necesidad de Elasticsearch para busqueda simple
  - Los triggers de auditoria son mas confiables que auditoria a nivel aplicacion

Como manejo migraciones de esquema en produccion?

Usa migraciones online compatibles hacia atras. Agrega columnas nullable primero, popula datos, luego agrega constraints. Nunca bloques la tabla durante migraciones. Usa CREATE INDEX CONCURRENTLY en PostgreSQL. Para renombrar columnas, agrega la nueva, sincroniza con triggers, migra el codigo, luego elimina la vieja. Herramientas como Flyway o Liquibase automatizan el versionado de migraciones.

End of document. Review and update quarterly.

Troubleshooting

• Query is slow after an index change: check execution plans and cardinality estimates. Rebuild statistics and verify the index is being used.
• Replication lag grows: Split large writes and consider parallel replication.
• Connections exhausted: review connection pool size, idle timeouts, and leaked connections.
• Backup takes too long: enable compression, incremental backups, and off-peak scheduling.
• Deadlocks in high concurrency: access tables and rows in a consistent order.

Errores Comunes en Producción

• Tratar la guía como un checklist para completar una vez en lugar de una práctica por evolucionar.
• Adoptar cada recomendación de golpe en lugar de comenzar con un cambio medido.
• Saltar la evaluación de madurez e imponer prácticas avanzadas a un equipo no preparado.
• No actualizar runbooks y expectativas de guardia al introducir nuevas prácticas.
• Ignorar datos reales de incidentes al priorizar qué partes de la guía aplicar primero.
• No asignar un responsable que revise decisiones trimestralmente.
• Copiar ejemplos sin adaptarlos a las herramientas y restricciones reales del equipo.
• Olvidar medir resultados antes de agregar la siguiente mejora.