Diseño de tablas y normalización (esquema de 3 capas)

El dataset de este artículo — orders_flat (un ejemplo sin normalizar)

La normalización (una técnica de diseño que divide tablas para reducir datos duplicados e inconsistentes) es la idea de descomponer una única tabla gigante que lo contiene todo en varias tablas más fáciles de mantener consistentes.

En este artículo verás los problemas que ocurren en una tabla sin normalizar con columnas repetidas, y luego construirás una comprensión de 1NF / 2NF / 3NF y del esquema de 3 capas mediante ejercicios prácticos de diseño.

El dataset es orders_flat (2 filas), que mete los datos de pedidos en una sola tabla.

Es una tabla clásica sin normalizar donde el nombre del cliente, el teléfono, el producto 1 y el producto 2 están todos alineados en una sola fila — y la normalizarás en las tres tablas customer / order_record / order_line.

Antes de pasar a los ejercicios, echa un vistazo a las definiciones de columnas y los datos de muestra de la tabla orders_flat.

① Ejecuta PRAGMA table_info(orders_flat); para revisar los nombres y tipos de columna. Fíjate en cómo las columnas de producto aparecen como columnas repetidas del estilo item1 / item2.

② Ejecuta SELECT * FROM orders_flat; para previsualizar todas las filas. Observa también cómo las filas con un solo producto acaban con una cadena vacía o NULL en item2.

Tres anomalías en una tabla sin normalizar

Un diseño como orders_flat, que mete todo en una sola tabla, produce contradicciones fácilmente cuando añades, cambias o eliminas datos.

Los ejemplos clásicos son tres: anomalía de actualización / anomalía de inserción / anomalía de eliminación.

Una anomalía de actualización ocurre cuando el mismo dato se duplica por muchas filas — por ejemplo, si quieres cambiar un número de teléfono, tienes que actualizar todas las filas sin saltarte ninguna.

Una anomalía de inserción es cuando no puedes registrar un cliente que aún no ha hecho ningún pedido, porque no hay ninguna fila de pedido a la que pegar la información del cliente.

Una anomalía de eliminación es cuando eliminar un solo pedido también borra información que querías conservar, como el teléfono del cliente.

Esto ocurre porque el diseño no mantiene "un dato en un solo sitio".

-- Ejemplo de anomalía de eliminación: eliminar el pedido 2 también borra la info de contacto de Bob
-- (observa el comportamiento en una tabla desechable flat_demo para que la orders_flat real no se toque)
DROP TABLE IF EXISTS flat_demo;
CREATE TABLE flat_demo(
  order_id INTEGER, customer TEXT, customer_tel TEXT, product TEXT
);
INSERT INTO flat_demo VALUES
  (1,'Alice','03-1111','Pen'),
  (2,'Bob','03-2222','Clip');

-- Bob solo tiene un pedido. Eliminarlo también pierde el teléfono 03-2222 de Bob
DELETE FROM flat_demo WHERE order_id = 2;
SELECT * FROM flat_demo;
-- -> La info de contacto de Bob ya no está en la BD (anomalía de eliminación)

Reproduce en una tabla desechable la situación en la que "el teléfono de Alice ha cambiado y quieres actualizarlo, pero el mismo teléfono está duplicado en varias filas". Este ejercicio se sostiene por sí mismo — crearás una tabla dedicada flat_anomaly para mantenerlo autocontenido. (Si lo ejecutas correctamente, aparecerá la explicación.)

① DROP TABLE IF EXISTS la tabla flat_anomaly, luego CREATE con 4 columnas: order_id / customer / customer_tel / product.

② Inserta 2 filas para los pedidos de Alice (ambos con el mismo teléfono 03-1111; los productos son Pen y Note).

③ Ejecuta un UPDATE que cambie el teléfono de Alice a 03-9999 pero que solo afecte a una fila (por ejemplo con una condición como WHERE order_id = 1), luego ejecuta SELECT * FROM flat_anomaly; y confirma que los teléfonos de las dos filas no coinciden.

De 1NF a 3NF — divide paso a paso

Las tablas se organizan por etapas mediante normalización.

1NF — deshazte de las columnas repetidas

Elimina las columnas repetidas numeradas del estilo item1 / item2.

Antes — columnas repetidas item1 / item2

Después — una fila por producto, con la clave primaria compuesta `(order_id, product)`

2NF — mueve las columnas que dependen solo de una parte de la clave primaria a otra tabla

La tabla del paso anterior tiene la clave primaria compuesta (order_id, product). Pero fíjate bien: customer y tel están determinados solo por `order_id` (el mismo order_id siempre tiene el mismo cliente).

Mover esas columnas que dependen de solo una parte de la clave primaria (dependencia parcial) a una tabla aparte es 2NF. La divides en una cabecera de pedido order_header y los detalles del pedido order_line.

Antes — customer y tel duplicados en cada fila

Después — dividido en 2 tablas

order_header (una fila por pedido)

order_line (clave primaria compuesta (order_id, product), donde order_id es clave foránea a order_header)

3NF — mueve a otra tabla las columnas determinadas por columnas que no son clave

Mirando el order_header del paso anterior, tel no está determinado por order_id sino por `customer` (la misma Alice siempre tiene 03-1111).

Eliminar las dependencias que pasan por otra columna que no es clave en lugar de hacerlo directamente desde la clave primaria (dependencia transitiva: order_id -> customer -> tel) es de lo que va 3NF.

Extraes un maestro de cliente customer y dejas order_record conservando solo la clave foránea al cliente.

Antes — tel depende de customer

Después — dividido en 3 tablas

customer (maestro de cliente)

order_record (cabecera de pedido: referencia al cliente mediante clave foránea)

order_line (detalles de pedido: igual que en el paso anterior)

Una vez dividido hasta aquí, cada dato (el teléfono de un cliente, el vínculo entre pedido y cliente, la cantidad de un producto en un pedido) vive en un único lugar. Eso elimina las anomalías de actualización, inserción y eliminación.

El esquema de 3 capas y la forma final — customer / order_record / order_line

El esquema normalizado se puede organizar como tres capas con roles diferentes.

• Capa maestra: entidades que cambian con poca frecuencia y son referenciadas por otras, como customer
• Capa transaccional: tablas que registran eventos de negocio, como order_record
• Capa de detalle: tablas que contienen las filas de desglose de un evento, como order_line

Al apuntar el lado que referencia hacia la maestra con una clave foránea, cada dato (como el teléfono de un cliente) acaba en un solo sitio.

La forma final son estas tres tablas:

customer(customer_id clave primaria, customer_name, customer_tel), order_record(order_id clave primaria, customer_id -> customer), order_line(order_id -> order_record, product, qty, clave primaria compuesta (order_id, product)).

El teléfono vive en una sola fila de customer, así que una actualización es de una sola fila; un cliente sin pedidos aún puede añadirse a customer (anomalía de inserción resuelta); y eliminar un pedido deja al cliente en customer (anomalía de eliminación resuelta).

-- Ventaja de la normalización (solo lectura): el teléfono de un cliente está en una sola fila de customer
-- En la forma orders_flat tendrías que arreglar todas las filas de pedido de Alice = anomalía de actualización
UPDATE customer SET customer_tel = '03-9999'
WHERE customer_name = 'Alice';

-- Arregla un solo punto y el nuevo número fluye a cada pedido en el JOIN
SELECT o.order_id, c.customer_name, c.customer_tel
FROM order_record o
JOIN customer c ON o.customer_id = c.customer_id
ORDER BY o.order_id;

Imagina el requisito: "Normaliza orders_flat en tres tablas — cliente, pedido y detalles del pedido". Diseña el esquema final con claves foráneas que las enlacen, traslada los datos y reconstruye la lista original con un JOIN.

① Activa la comprobación de claves foráneas, luego haz DROP TABLE IF EXISTS en el orden order_line -> order_record -> customer (eliminar primero la hija es más seguro).

② Haz CREATE de la tabla customer (customer_id INTEGER clave primaria / customer_name TEXT NOT NULL / customer_tel TEXT).

③ Haz CREATE de la tabla order_record (order_id INTEGER clave primaria / customer_id INTEGER NOT NULL, clave foránea a customer).

④ Haz CREATE de la tabla order_line (order_id INTEGER, clave foránea a order_record / product TEXT NOT NULL / qty INTEGER NOT NULL / clave primaria compuesta (order_id, product)).

⑤ Inserta Alice(1, 03-1111) y Bob(2, 03-2222) en customer; pedido 1 -> cliente 1 y pedido 2 -> cliente 2 en order_record; Pen(pedido 1, 2) / Note(pedido 1, 1) / Clip(pedido 2, 5) en order_line.

⑥ Une las 3 tablas por order_id y customer_id y selecciona order_id / customer_name / customer_tel / product / qty en orden ascendente de order_id y product para confirmar que puedes reconstruir la lista original.

Usando las tablas customer / order_record / order_line del Ejercicio 2, confirma una por una que las anomalías de actualización, inserción y eliminación que plagaban la tabla sin normalizar ya no ocurren. Este es el último ejercicio del artículo.

① Anomalía de actualización resuelta: Cambia el teléfono de Alice a '03-9999'. Confirma que es un UPDATE de una sola fila sobre la tabla customer.

② Anomalía de inserción resuelta: INSERTA un nuevo cliente Charlie (customer_id 3 / customer_name 'Charlie' / customer_tel '03-3333') en customer. Confirma que puedes registrar un cliente sin pedidos.

③ Anomalía de eliminación resuelta: Elimina el pedido 1. Elimina primero del hijo order_line y luego de order_record. Ejecuta SELECT * FROM customer ORDER BY customer_id; y confirma que la fila de Alice sigue en customer.

Tips — Pon siempre una clave primaria en cada tabla

Hayas normalizado o no, diseña cada tabla con una clave primaria (`PRIMARY KEY`). Una clave primaria te da tres cosas:

- Identificación única de fila: dos filas no pueden tener la misma clave, así que UPDATE / DELETE apuntan de forma fiable a exactamente una fila mediante WHERE pk = ...

- Referenciable desde otras tablas: una clave foránea necesita una clave primaria (o UNIQUE) del otro lado. Es la base para enlazar las tablas que divides al normalizar

- Indexación automática: las columnas de clave primaria reciben un índice B-Tree de forma automática, acelerando las búsquedas por igualdad y los JOINs

Si ninguna columna individual es única por sí sola, usa una clave primaria compuesta como PRIMARY KEY (order_id, product). Saltártela con "ya la añadiré luego" hace indistinguibles las filas duplicadas y renuncia a las tres ventajas anteriores.