Cómo se diseña una base de datos

Llevas nueve capítulos consultando una base que alguien ya diseñó. Hoy toca el otro lado 🛠️

Y es el capítulo que casi ningún curso da, porque enseñar SELECT se ve rápido y enseñar a diseñar se ve lento. Pero es al revés de lo que parece: una base mal diseñada no se arregla con consultas. Se arregla rehaciéndola, con los datos dentro, y eso sí es lento.

Antes de arrancar, contéstate una: ¿alguna vez abriste un Excel del trabajo donde la misma información estaba en tres hojas distintas? Si la respuesta es sí, ya sabes lo que se siente vivir sin diseño. Este capítulo va de por qué pasa eso y de cómo se evita 🗂️

La regla que ordena casi todo

Una tabla por cada cosa que existe de verdad. Nada más.

Un cliente existe. Un pedido existe. Un producto existe. Cada uno es una tabla. Y lo que no es una cosa sino una relación entre cosas, como "este pedido lleva estos productos", también termina siendo una tabla, pero de otro tipo. Ya la vamos a ver.

Mira cómo está armada la base con la que llevas nueve capítulos:

SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;

name
-----------
clientes
detalle
direcciones
pedidos
productos

Cinco tablas y cinco cosas: los clientes, sus direcciones, los pedidos, los productos y las líneas de cada pedido. Nadie decidió eso al azar. Es el resultado de hacerse una pregunta por cada columna: ¿esto describe a la fila o es otra cosa que existe por su cuenta? 🤔

La clave primaria, o cómo se llama cada fila

Cada tabla necesita una columna que identifique la fila y no se repita nunca. Eso es la clave primaria, y no es un adorno: es lo que hace que la base pueda decir "esta fila y no otra".

Mira lo que pasa sin ella:

CREATE TABLE clientes_mal (
    id     INTEGER,
    nombre TEXT,
    ciudad TEXT
);

INSERT INTO clientes_mal VALUES (1, 'Bodega Aurora', 'Lima');
INSERT INTO clientes_mal VALUES (1, 'Bodega Aurora', 'Lima');

SELECT COUNT(*) AS filas FROM clientes_mal;

filas
-----
2

Dos filas para un cliente 😐 La base no se quejó porque nadie le dijo que id tenía que ser único. Y esto no se queda ahí, mira lo que le hace a un total:

CREATE TABLE pedidos_p (
    id         INTEGER PRIMARY KEY,
    id_cliente INTEGER,
    monto      REAL
);
INSERT INTO pedidos_p VALUES (1, 1, 100.0), (2, 1, 250.0);

SELECT COUNT(*) AS pedidos, SUM(monto) AS soles FROM pedidos_p;

pedidos  soles
-------  -----
2        350.0

SELECT COUNT(*) AS pedidos, SUM(p.monto) AS soles
FROM pedidos_p p
JOIN clientes_mal c ON c.id = p.id_cliente;

pedidos  soles
-------  -----
4        700.0

Dos pedidos de 350 soles se convirtieron en cuatro de 700 💸 Es el mismo fan-out del capítulo 7, pero acá la causa no fue el JOIN: fue que la tabla permitía el duplicado desde el día uno.

Con la clave primaria puesta, el segundo INSERT ni entra:

CREATE TABLE clientes_bien (
    id     INTEGER PRIMARY KEY,
    nombre TEXT NOT NULL,
    ciudad TEXT
);
INSERT INTO clientes_bien VALUES (1, 'Bodega Aurora', 'Lima');
INSERT INTO clientes_bien VALUES (1, 'Bodega Aurora', 'Lima');

IntegrityError: UNIQUE constraint failed: clientes_bien.id

Ese error es una buena noticia 🎉 La base te frenó en el momento en que se podía arreglar, en vez de dejarte descubrirlo tres meses después en un reporte que no cuadra.

Y cuál columna eliges

La regla que uso: una clave primaria no debe significar nada. Un número que solo sirve para identificar la fila.

La tentación es usar algo que ya tienes, como el DNI, el RUC o el correo. Y falla siempre por lo mismo: esas cosas cambian. El correo se cambia, el RUC se corrige porque estaba mal tecleado, y el día que cambia tienes que ir a actualizarlo en las siete tablas que lo estaban guardando. Un id que no significa nada no cambia nunca, porque no hay nada que pueda estar mal en él.

La clave foránea, o cómo se enganchan dos tablas

Una tabla apunta a otra guardando su clave primaria. Ya está, eso es todo el modelo relacional.

La tabla pedidos no guarda el nombre del cliente. Guarda su id. Y esa columna se llama clave foránea porque es la clave de otra tabla, no la suya.

SELECT COUNT(*) AS clientes FROM clientes;

clientes
--------
120

SELECT COUNT(*) AS pedidos_sin_cliente
FROM pedidos p
LEFT JOIN clientes c ON c.id = p.id_cliente
WHERE c.id IS NULL;

pedidos_sin_cliente
-------------------
24

Veinticuatro pedidos que apuntan a nadie 👻 En esta base pasa porque el id_cliente viene en NULL, y porque SQLite trae las claves foráneas apagadas de fábrica, que es una de las cosas que vamos a ver de cerca en el capítulo 11. En una base con la clave foránea encendida, esas 24 filas no habrían podido entrar.

Y por eso vale la pena escribirla aunque tu motor no la aplique: el REFERENCES es documentación que además, en los otros tres motores, se cumple sola.

Uno a muchos, que es casi todo lo que vas a ver

Un cliente tiene muchos pedidos. Un pedido tiene un solo cliente. Eso es una relación de uno a muchos, y se resuelve poniendo la clave del lado "uno" en la tabla del lado "muchos".

La pregunta que decide dónde va la clave es siempre la misma: ¿de qué lado puede haber varios? Ahí va la columna.

SELECT c.nombre, COUNT(d.id) AS direcciones
FROM clientes c
JOIN direcciones d ON d.id_cliente = c.id
GROUP BY c.id
ORDER BY direcciones DESC, c.nombre
LIMIT 4;

nombre                          direcciones
------------------------------  -----------
  MARKET CENTRAL 012            3
  RESTAURANTE MIRAFLORES 070    3
Almacenes Vega 067              3
Autoservicio Norte 088          3

Hay clientes con tres direcciones. Por eso la dirección es su propia tabla y no tres columnas en clientes 📍

Y fíjate en el detalle de las columnas repetidas, porque es el error que más he visto: direccion1, direccion2, direccion3. Funciona hasta que aparece el cliente con cuatro. Y entonces hay que cambiar la tabla, cambiar todas las consultas y cambiar el formulario. Si necesitas numerar columnas, lo que necesitas es otra tabla.

Muchos a muchos, y la tabla que aparece en medio

Un pedido lleva varios productos. Y un producto está en varios pedidos. Los dos lados son "muchos", así que la clave no cabe en ninguna de las dos tablas 🤷

Se resuelve con una tercera tabla que solo existe para guardar la relación. En esta base se llama detalle:

SELECT COUNT(*) AS lineas,
       COUNT(DISTINCT id_pedido)   AS pedidos,
       COUNT(DISTINCT id_producto) AS productos
FROM detalle;

lineas  pedidos  productos
------  -------  ---------
2682    900      40

2.682 líneas para conectar 900 pedidos con 40 productos. Cada línea dice "este pedido lleva este producto", y de paso guarda lo que solo tiene sentido en el cruce: cuántas unidades y a qué precio se vendió ese día 💡

Esa es la señal de que la tabla puente está bien pensada: guarda cosas que no pertenecen a ninguno de los dos lados. La cantidad no es del producto ni del pedido: es de la línea.

Las tres reglas de normalización, sin jerga

Normalizar suena a examen y son tres ideas de sentido común. Te las cuento como las uso yo 🌸

• Una celda, un dato. Nada de meter tres cosas separadas por comas en la misma columna. (Primera forma normal.)
• Cada columna describe a la fila entera. Si una columna solo depende de una parte de la clave, es de otra tabla. (Segunda.)
• Ninguna columna describe a otra columna. Si guardas la ciudad y también el departamento, el departamento describe a la ciudad y no al cliente. (Tercera.)

La primera es la que más se rompe y la que más caro sale, así que le vamos a dedicar la trampa del capítulo entera.

Tipos de base de datos, y cuándo no quieres una relacional

Todo este libro va de bases relacionales, que son las que usa el 100% de los trabajos donde vas a escribir SQL. Pero hay otras, y conviene saber que existen para no pedir una por moda 🧭

Y te doy mi opinión, que en esto tengo una clara: si dudas, es relacional. La mayoría de los proyectos que eligen otra cosa lo hacen porque suena moderno, y terminan reinventando a mano las relaciones que la base relacional les daba gratis 💜

Cuándo se desnormaliza a propósito

Todo lo anterior tiene una excepción y no es contradicción, es un cambio de objetivo.

Normalizas para escribir: que un dato viva en un solo sitio y no puedas contradecirte. Desnormalizas para leer: repites un dato a propósito para no tener que juntar seis tablas cada vez.

Por eso un almacén de datos para reportes casi siempre está desnormalizado, y la base donde se factura casi nunca. Son dos bases con dos trabajos distintos, y esa diferencia la contamos en el capítulo 2.

La regla corta: desnormalizar es una decisión, nunca un descuido. Si repites un dato, que sea porque lo decidiste y sepas quién lo mantiene al día 🎯

Ejercicios

Los ejercicios son la mitad del libro. Intenta antes de abrir.

SELECT m.name AS tabla, i.name AS columna
FROM sqlite_master m
JOIN pragma_table_info(m.name) i
WHERE m.type = 'table' AND i.pk = 1
ORDER BY m.name;

tabla          columna
-------------  -------
clientes       id
clientes_bien  id
detalle        id
direcciones    id
pedidos        id
pedidos_p      id
productos      id

SELECT COUNT(*) AS direcciones,
       COUNT(DISTINCT id_cliente) AS clientes,
       ROUND(COUNT(*) * 1.0 / COUNT(DISTINCT id_cliente), 2) AS media
FROM direcciones;

direcciones  clientes  media
-----------  --------  -----
164          120       1.37

SELECT p.nombre, COUNT(DISTINCT d.id_pedido) AS pedidos
FROM detalle d
JOIN productos p ON p.id = d.id_producto
GROUP BY p.id
ORDER BY pedidos DESC
LIMIT 5;

nombre       pedidos
-----------  -------
Producto 38  79
Producto 22  77
Producto 13  77
Producto 29  75
Producto 10  75

CREATE TABLE vendedores (
    id     INTEGER PRIMARY KEY,
    nombre TEXT NOT NULL,
    zona   TEXT
);

ALTER TABLE pedidos ADD COLUMN id_vendedor INTEGER REFERENCES vendedores(id);

SELECT COUNT(*) AS columnas FROM pragma_table_info('pedidos');

columnas
--------
6

SELECT ciudad, COUNT(*) AS clientes
FROM clientes
GROUP BY ciudad
ORDER BY clientes DESC;

ciudad    clientes
--------  --------
Chiclayo  25
Piura     24
Arequipa  22
Trujillo  17
Cusco     17
Lima      15

CREATE TABLE reclamos (
    id        INTEGER PRIMARY KEY,
    id_ticket INTEGER REFERENCES tickets(id),
    detalle   TEXT
);

INSERT INTO reclamos VALUES (1, 99, 'llegó incompleto');
SELECT COUNT(*) AS reclamos FROM reclamos;

reclamos
--------
1

SELECT COUNT(DISTINCT precio_unit) AS precios_distintos,
       COUNT(DISTINCT id_producto) AS productos
FROM detalle;

precios_distintos  productos
-----------------  ---------
40                 40

La columna que guarda tres cosas

Antes de cerrar, la trampa del capítulo. Es la primera forma normal rota, que es la regla que más se rompe y la que más caro sale 🍬

-- categorias: 'Bebidas' / 'Bebidas,Sin azucar'
-- 'Snacks,Bebidas calientes' / 'Bebidas,Snacks'

SELECT COUNT(*) FROM prod WHERE categorias = 'Bebidas';
-- 1

SELECT COUNT(*) FROM prod WHERE categorias LIKE '%Bebidas%';
-- 4

Lo que te llevas

• 🗂️ Una tabla por cada cosa que existe de verdad.
• 🔑 Clave primaria en todas, y que no signifique nada. El DNI y el correo cambian, un id no.
• 🔗 Uno a muchos se resuelve poniendo la clave en el lado donde hay varios. La pregunta es siempre "¿de qué lado puede haber varios?".
• 🌉 Muchos a muchos necesita una tabla en medio, y esa tabla guarda lo que no es de ninguno de los dos lados.
• 🔢 Si necesitas numerar columnas (direccion1, direccion2), lo que necesitas es otra tabla.
• 🧼 Las tres reglas: una celda un dato, cada columna describe a la fila entera, ninguna columna describe a otra columna.
• 🧭 Si dudas del tipo de base, es relacional.
• 🎯 Desnormalizar es una decisión, nunca un descuido.

Y si de todo el capítulo te llevas una sola frase, que sea esta:

Y esto de decidir qué columna existe, de qué tipo y qué se hace con lo que falta es media conversación con el cliente antes de tocar nada. Tiene nombre y está contado en la guía de machine learning: el contrato de datos 🤝

En el capítulo 11 pasamos del papel al teclado: CREATE TABLE de verdad, con sus tipos, sus restricciones y los cuatro autoincrementos que cada motor llama distinto.

Que tengas lindo día! 🌸