Diseño: E-R y normalización

Saber escribir consultas es la mitad del trabajo. La otra mitad, y la más importante cuando un cliente te encarga un sistema, es diseñar la base de datos. Un mal diseño se paga durante años: datos duplicados, inconsistencias, consultas imposibles. En esta lección aprenderás a modelar con el modelo entidad-relación y a limpiar tus tablas con la normalización (1FN, 2FN y 3FN).

Concepto

Diseñar una base de datos es como hacer los planos de una casa antes de construir: decides cuántos cuartos (tablas) habrá, qué hay en cada uno (columnas) y cómo se conectan (relaciones). Mover una pared en el plano es gratis; en la casa terminada, carísimo.

El proceso tiene tres etapas:

txt

1. Modelo conceptual        2. Modelo lógico              3. Modelo físico
   (diagrama E-R)              (tablas, PK, FK)              (CREATE TABLE con tipos,
   "¿qué cosas existen          "¿cómo lo represento          índices, motor...)
    y cómo se relacionan?"       en tablas?"

El modelo entidad-relación (E-R)

Entidades y atributos

Entidad: una "cosa" del negocio de la que guardas información: Cliente, Producto, Pedido. Se convierte en tabla.
Atributo: una característica de la entidad: nombre, precio, fecha. Se convierte en columna.
Identificador: el atributo que distingue a cada ejemplar. Se convierte en PK.

Relaciones y cardinalidad

Una relación es un verbo que conecta entidades: un cliente realiza pedidos. La cardinalidad dice cuántos de cada lado:

Cardinalidad	Lectura	Ejemplo	Cómo se implementa
1 : 1	Uno con uno	Usuario ↔ Perfil	FK única en una de las tablas (o misma tabla)
1 : N	Uno con muchos	Cliente → Pedidos	FK en el lado "muchos" (`pedidos.cliente_id`)
N : M	Muchos con muchos	Pedidos ↔ Productos	Tabla intermedia con dos FK (`detalle_pedido`)

Notación de "pata de gallo" en texto

Es la notación más usada en herramientas como MySQL Workbench, dbdiagram.io o draw.io:

txt

Símbolos:   ──┼──  exactamente uno        ──○┼──  cero o uno
            ──<    muchos                 ──○<    cero o muchos
            ──┼<   uno o muchos

El diagrama de nuestra tienda:

txt

┌───────────┐                ┌───────────┐               ┌──────────────┐
│ CATEGORIA │ ┼○──────────○< │ PRODUCTO  │ ┼┼─────────○< │DETALLE_PEDIDO│
│───────────│  una categoría │───────────│  un producto  │──────────────│
│ id (PK)   │  tiene 0..N    │ id (PK)   │  aparece en   │ pedido_id    │
│ nombre    │  productos;    │ nombre    │  0..N detalles│ producto_id  │
└───────────┘  un producto   │ precio    │               │ cantidad     │
               tiene 0..1    │ stock     │               │ precio_unit. │
               categoría     └───────────┘               └──────┬───────┘
                                                                │ >┼
┌───────────┐                ┌───────────┐                      │ cada detalle
│ CLIENTE   │ ┼┼─────────○<  │ PEDIDO    │ ┼┼───────────────────┘ pertenece a
│───────────│  un cliente    │───────────│  un pedido tiene       un pedido
│ id (PK)   │  hace 0..N     │ id (PK)   │  1..N detalles
│ nombre    │  pedidos       │ fecha     │
│ email     │                │ estado    │
└───────────┘                └───────────┘

Ejemplos de cada cardinalidad

1 : 1 — Cada usuario tiene un perfil con datos opcionales:

sql

CREATE TABLE usuarios (
  id INT PRIMARY KEY,
  email VARCHAR(150) NOT NULL UNIQUE
);
CREATE TABLE perfiles (
  usuario_id INT PRIMARY KEY,          -- la PK es también FK: garantiza 1:1
  biografia TEXT,
  sitio_web VARCHAR(200),
  FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE
);

1 : N — Ya lo tienes: pedidos.cliente_id.

N : M — Un alumno se inscribe en muchas materias y una materia tiene muchos alumnos:

sql

CREATE TABLE alumnos  (id INT PRIMARY KEY, nombre VARCHAR(100) NOT NULL);
CREATE TABLE materias (id INT PRIMARY KEY, nombre VARCHAR(100) NOT NULL);

CREATE TABLE inscripciones (
  alumno_id INT NOT NULL,
  materia_id INT NOT NULL,
  fecha DATE NOT NULL,                 -- la relación puede tener atributos propios
  calificacion DECIMAL(4,2),
  PRIMARY KEY (alumno_id, materia_id),
  FOREIGN KEY (alumno_id)  REFERENCES alumnos(id),
  FOREIGN KEY (materia_id) REFERENCES materias(id)
);

Buena práctica

Cuando una relación N:M tiene atributos propios (cantidad, calificación, fecha), esa tabla intermedia es en realidad una entidad importante del negocio. Dale un buen nombre (inscripciones, detalle_pedido) en lugar de alumno_materia.

Paso a paso: del requerimiento al diagrama

Un cliente te dice: "Tengo una clínica veterinaria. Los dueños traen a sus mascotas a consulta con alguno de nuestros veterinarios. En cada consulta se registra el diagnóstico y se pueden recetar varios medicamentos con su dosis."

Sustantivos → entidades: Dueño, Mascota, Veterinario, Consulta, Medicamento.
Atributos: Dueño (nombre, teléfono), Mascota (nombre, especie, fecha de nacimiento), Consulta (fecha, diagnóstico)...
Verbos → relaciones:
- Un dueño tiene muchas mascotas; una mascota tiene un dueño → 1:N.
- Una mascota asiste a muchas consultas → 1:N.
- Un veterinario atiende muchas consultas → 1:N.
- Una consulta receta muchos medicamentos y un medicamento se receta en muchas consultas → N:M con atributo dosis.
Tablas resultantes:

txt

duenos        (PK id, nombre, telefono, email)
mascotas      (PK id, FK dueno_id, nombre, especie, fecha_nacimiento)
veterinarios  (PK id, nombre, cedula UNIQUE)
consultas     (PK id, FK mascota_id, FK veterinario_id, fecha, diagnostico)
medicamentos  (PK id, nombre, presentacion)
recetas       (PK/FK consulta_id, PK/FK medicamento_id, dosis, dias)

Valida con preguntas reales: "¿Qué medicamentos le recetaron a Firulais en marzo?" → consultas + recetas + medicamentos. Si puedes responderla con JOINs, el diseño va bien.

Normalización

La normalización es un conjunto de reglas para eliminar redundancia y anomalías. Cada "forma normal" (FN) es un nivel; para cumplir la 2FN debes cumplir la 1FN, y así.

El punto de partida: una tabla mal diseñada

Así guardaría los pedidos alguien que viene de Excel:

pedidos_excel

pedido	fecha	cliente	email_cliente	ciudad	productos	categorias	total
1	2024-05-02	Ana López	ana@correo.com	CDMX	Audífonos x1, Libro SQL x2	Electrónica, Libros	1597
2	2024-05-10	Carlos Ruiz	carlos@correo.com	Guadalajara	Teclado x1, Taza x3	Electrónica, Hogar	1847
3	2024-06-01	Ana López	ana@gmail.com	CDMX	Monitor x1	Electrónica	3499

Problemas (anomalías):

De actualización: el email de Ana está repetido... y ya es distinto en el pedido 3. ¿Cuál es el bueno?
De inserción: no puedes registrar un producto nuevo hasta que alguien lo compre.
De borrado: si borras el pedido 2, pierdes que existe el producto "Teclado" y el cliente Carlos.
Consultas imposibles: "¿cuántas tazas se vendieron?" requiere partir textos.

Primera Forma Normal (1FN): valores atómicos

Regla: cada celda contiene un solo valor (atómico) y no hay grupos repetidos (producto1, producto2, producto3...). Además, cada fila es identificable.

La columna productos con "Audífonos x1, Libro SQL x2" viola la 1FN. Separamos un renglón por producto:

pedidos_1fn (PK: pedido + producto)

pedido	fecha	cliente	email_cliente	ciudad	producto	categoria	precio	cantidad
1	2024-05-02	Ana López	ana@correo.com	CDMX	Audífonos	Electrónica	899	1
1	2024-05-02	Ana López	ana@correo.com	CDMX	Libro SQL	Libros	349	2
2	2024-05-10	Carlos Ruiz	carlos@correo.com	Guadalajara	Teclado	Electrónica	1250	1
2	2024-05-10	Carlos Ruiz	carlos@correo.com	Guadalajara	Taza	Hogar	199	3
3	2024-06-01	Ana López	ana@correo.com	CDMX	Monitor	Electrónica	3499	1

Ahora se puede consultar, pero hay mucha repetición. Además quitamos total: es un dato calculable (SUM(cantidad * precio)).

Segunda Forma Normal (2FN): depender de toda la clave

Regla: estar en 1FN y que cada columna que no es clave dependa de la clave primaria completa, no solo de una parte. (Solo aplica cuando la PK es compuesta.)

La PK es (pedido, producto). Analicemos:

Columna	¿Depende de...	Problema
`fecha`, `cliente`, `email`, `ciudad`	solo de `pedido`	Dependencia parcial ✘
`categoria`, `precio`	solo de `producto`	Dependencia parcial ✘
`cantidad`	de `pedido` + `producto`	Correcto ✔

Separamos cada grupo en su propia tabla:

txt

pedidos   (PK pedido, fecha, cliente, email_cliente, ciudad)
productos (PK producto, categoria, precio)
detalle   (PK pedido + producto, cantidad, precio_unitario)

¿Y el precio en el detalle?

precio_unitario en el detalle no es redundante: es el precio al momento de la venta, un dato histórico distinto del precio actual del producto. Es una decisión de negocio, no un error de normalización.

Tercera Forma Normal (3FN): sin dependencias transitivas

Regla: estar en 2FN y que ninguna columna no clave dependa de otra columna no clave. Todo debe depender "de la clave, de toda la clave y nada más que de la clave".

En pedidos (pedido, fecha, cliente, email_cliente, ciudad):

txt

pedido ──► cliente ──► email_cliente, ciudad
           (no clave)   ↑ dependen del cliente, no del pedido = transitiva ✘

El email y la ciudad son datos del cliente, no del pedido. En productos (producto, categoria, precio), si la categoría tuviera atributos (descripción, icono), también se separaría.

Resultado en 3FN... ¡es exactamente nuestro esquema del curso!

txt

clientes       (PK id, nombre, email, ciudad, fecha_registro)
categorias     (PK id, nombre)
productos      (PK id, nombre, FK categoria_id, precio, stock)
pedidos        (PK id, FK cliente_id, fecha, estado)
detalle_pedido (PK/FK pedido_id, PK/FK producto_id, cantidad, precio_unitario)

Cada dato vive en un solo lugar: cambiar el email de Ana es un UPDATE de una fila.

Resumen de las formas normales

Forma	Regla	Pregunta para detectarla
1FN	Valores atómicos, sin grupos repetidos	¿Hay listas o columnas `algo1, algo2, algo3`?
2FN	Sin dependencias parciales de una PK compuesta	¿Esta columna depende solo de parte de la clave?
3FN	Sin dependencias transitivas	¿Esta columna describe a otra columna en vez de a la clave?

Existen formas superiores (BCNF, 4FN, 5FN), pero en el trabajo diario la 3FN es la meta estándar.

Cuándo desnormalizar

A veces se rompe la normalización a propósito por rendimiento:

Desnormalización	Por qué	Costo
Guardar `total` en `pedidos`	Listar pedidos sin sumar el detalle cada vez	Mantenerlo sincronizado (transacción o trigger)
Contador `num_resenas` en `productos`	Mostrar "4.5 ★ (120)" sin `COUNT`	Actualizarlo al crear/borrar reseñas
Copiar la dirección de envío en el pedido	Histórico: el cliente puede mudarse	Más espacio (y es lo correcto)
Tablas de resumen para reportes	Dashboards rápidos	Datos con algo de retraso

Buena práctica

Normaliza primero hasta 3FN. Desnormaliza solo cuando midas un problema de rendimiento real, documenta la decisión y protege la copia con transacciones, triggers o vistas materializadas.

Error común: la tabla "Dios"

Una sola tabla datos con 80 columnas (dato1, dato2, tipo, extra...) donde se guarda todo. O el extremo opuesto: el modelo EAV (entidad, atributo, valor) para todo. Ambos hacen las consultas lentas y las reglas imposibles. Modela entidades reales del negocio.

Herramientas para diagramar

dbdiagram.io: escribes texto y genera el diagrama; exporta SQL.
MySQL Workbench: diagramas EER y forward engineering a SQL.
DBeaver y pgAdmin: generan el diagrama de una BD existente.
draw.io / diagrams.net: libre, con formas de pata de gallo.

Ejercicios

Indica la cardinalidad: a) país–capital, b) autor–libro (un libro puede tener varios autores), c) empleado–departamento (cada empleado en uno solo).
Normaliza hasta 3FN esta tabla de una escuela:

matricula	alumno	carrera	coordinador_carrera	materias	calificaciones
A01	Luis	Sistemas	Ing. Rojas	BD, Redes	9, 8
A02	Eva	Sistemas	Ing. Rojas	BD	10

Diseña las tablas (con PK y FK) para un blog: autores, entradas, categorías (una entrada puede tener varias categorías) y comentarios de lectores.

Ver solución

a) 1:1. b) N:M (tabla intermedia autores_libros). c) 1:N (un departamento tiene muchos empleados; FK departamento_id en empleados).
1FN: separar materias y calificaciones (una fila por alumno-materia). 2FN: alumno y carrera dependen solo de la matrícula; la calificación depende de matrícula + materia. 3FN: el coordinador depende de la carrera, no del alumno.

txt

carreras       (PK id, nombre, coordinador)
alumnos        (PK matricula, nombre, FK carrera_id)
materias       (PK id, nombre)
calificaciones (PK/FK matricula, PK/FK materia_id, calificacion)

sql

CREATE TABLE autores (id INT PRIMARY KEY, nombre VARCHAR(100) NOT NULL, email VARCHAR(150) NOT NULL UNIQUE);
CREATE TABLE categorias_blog (id INT PRIMARY KEY, nombre VARCHAR(50) NOT NULL UNIQUE);
CREATE TABLE entradas (
  id INT PRIMARY KEY,
  autor_id INT NOT NULL,
  titulo VARCHAR(200) NOT NULL,
  slug VARCHAR(200) NOT NULL UNIQUE,
  contenido TEXT NOT NULL,
  publicada_en TIMESTAMP,
  FOREIGN KEY (autor_id) REFERENCES autores(id)
);
CREATE TABLE entradas_categorias (
  entrada_id INT NOT NULL,
  categoria_id INT NOT NULL,
  PRIMARY KEY (entrada_id, categoria_id),
  FOREIGN KEY (entrada_id) REFERENCES entradas(id) ON DELETE CASCADE,
  FOREIGN KEY (categoria_id) REFERENCES categorias_blog(id) ON DELETE CASCADE
);
CREATE TABLE comentarios (
  id INT PRIMARY KEY,
  entrada_id INT NOT NULL,
  nombre_lector VARCHAR(100) NOT NULL,
  contenido TEXT NOT NULL,
  creado_en TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (entrada_id) REFERENCES entradas(id) ON DELETE CASCADE
);

Resumen

Diseña antes de programar: modelo conceptual (E-R) → lógico (tablas) → físico (SQL).
Entidades → tablas, atributos → columnas, identificadores → PK.
1:N se resuelve con una FK en el lado "muchos"; N:M con una tabla intermedia.
1FN: valores atómicos. 2FN: sin dependencias parciales. 3FN: sin dependencias transitivas.
Cada dato en un solo lugar; desnormaliza solo con una razón medida y documentada.

Diseño: E-R y normalización ​

Concepto ​

El modelo entidad-relación (E-R) ​

Entidades y atributos ​

Relaciones y cardinalidad ​

Notación de "pata de gallo" en texto ​

Ejemplos de cada cardinalidad ​

Paso a paso: del requerimiento al diagrama ​

Normalización ​

El punto de partida: una tabla mal diseñada ​

Primera Forma Normal (1FN): valores atómicos ​

Segunda Forma Normal (2FN): depender de toda la clave ​

Tercera Forma Normal (3FN): sin dependencias transitivas ​

Resumen de las formas normales ​

Cuándo desnormalizar ​

Herramientas para diagramar ​

Ejercicios ​

Resumen ​