Claves naturales y desnormalización

En resumen: Una clave natural sale de los propios datos, como un correo; una clave sustituta es un número sin significado, y la desnormalización es una copia de datos que guardas a propósito.

Problema

María cambió su correo

Si diseñas tú los clientes, el correo parece una buena clave primaria. Es único, todo cliente tiene uno, es con lo que inicia sesión. Aquí hay un diseño así: los clientes tienen el correo como clave, y los pedidos apuntan al correo.

customers

• PKemailtext
• nametext *
• citytext

orders

• PKidinteger
• FKcustomer_emailtext *→ customers
• ordered_attext *

María cambia su correo:

SQLbase de datos vacía

UPDATE customers SET email = 'maria.i@example.com'
WHERE email = 'maria.ivanova@example.com';

UPDATE customers SET email = 'maria.i@example.com' WHERE email = 'maria.ivanova@example.com';

▶ EjecutarRestaurar la consultaBase nueva

La base de datos rechaza el cambio, porque tres pedidos apuntan al correo antiguo. Si cambias también esos, quedan las facturas ya enviadas, los informes y todos los demás sistemas que recordaron la clave antigua.

La idea

Claves naturales y sustitutas

Una clave tomada del mundo (un correo, un teléfono, el número de un libro) es una clave natural. Un número que la base de datos asigna sola, sin significado fuera de ella, es una clave sustituta. El id de la tienda es una.

Una clave sustituta nunca cambia, porque nada en el mundo la cambia. Así el correo cambia en una fila, y los pedidos ni se enteran:

SQLbase de datos: tienda

UPDATE customers SET email = 'maria.i@example.com' WHERE id = 1;

SELECT c.name, c.email, COUNT(*) AS orders
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 1
GROUP BY c.id, c.name, c.email;

UPDATE customers SET email = 'maria.i@example.com' WHERE id = 1; SELECT c.name, c.email, COUNT(*) AS orders FROM customers c JOIN orders o ON o.customer_id = c.id WHERE c.id = 1 GROUP BY c.id, c.name, c.email;

▶ EjecutarRestaurar la consultaBase nueva

El correo conserva su regla. Ya no es la clave, pero es UNIQUE, así que dos clientes no pueden tener el mismo correo:

SQLbase de datos: tienda

UPDATE customers SET email = 'georgi.petrov@example.com' WHERE id = 1;

UPDATE customers SET email = 'georgi.petrov@example.com' WHERE id = 1;

▶ EjecutarRestaurar la consultaBase nueva

¿Y un número de identificación nacional (en Bulgaria, el EGN)? No. Son datos personales, y la clave se copia en cada tabla que apunta al cliente. Un cliente del extranjero normalmente no lo tiene. Y la tienda no necesita saberlo.

Más precisamente, para curiosos

ON UPDATE CASCADE en REFERENCES hace que la base de datos cambie también las filas que apuntan a la clave antigua. Eso ayuda dentro de la base de datos, pero no con las copias de la clave fuera de ella: en correos, en registros, en otro sistema.

Una clave natural encaja cuando el valor realmente nunca cambia y se conoce desde fuera. Ejemplos son un código de moneda (EUR) o un código de país (BG).

Un UUID es otro tipo de clave sustituta: un número de 128 bits escrito como 550e8400-e29b-41d4-a716-446655440000. Se puede generar en cualquier parte, sin preguntar a la base de datos (en la aplicación, en un teléfono sin internet), dos bases de datos se pueden fusionar sin choques, y la dirección /orders/1043 ya no dice cuántos pedidos tiene la tienda. El costo es que es más largo (16 bytes frente a 8 de un entero grande), y los UUID aleatorios caen en lugares aleatorios del índice y lo ralentizan. Por eso existen también los UUID ordenados por tiempo (versión 7). PostgreSQL tiene un tipo uuid y una función gen_random_uuid(). SQLite no tiene un tipo aparte y lo guarda como texto o como BLOB.

Pruébalo

Un correo nuevo

El cliente 12, Boris, escribe que su nuevo correo es boris.m@example.com. Corrígelo.

SQLbase de datos: tienda

Comprobar▶ Ejecutar

La idea

El precio en la fila del pedido

order_items tiene unit_price, y products tiene price. Parece una repetición que la normalización quita. Sube el precio del café:

SQLbase de datos: tienda

UPDATE products SET price = 15.9 WHERE id = 17;

SELECT oi.order_id, oi.unit_price, p.price AS today
FROM order_items oi
JOIN products p ON p.id = oi.product_id
WHERE oi.product_id = 17;

UPDATE products SET price = 15.9 WHERE id = 17; SELECT oi.order_id, oi.unit_price, p.price AS today FROM order_items oi JOIN products p ON p.id = oi.product_id WHERE oi.product_id = 17;

▶ EjecutarRestaurar la consultaBase nueva

Los pedidos antiguos recuerdan 14,90 €, mientras el producto cuesta 15,90 €. No son dos copias de un hecho, sino dos hechos distintos: «lo que cuesta hoy» y «lo que pagó el cliente». Si el pedido tomara su precio de products, cada subida de precio cambiaría también las facturas pasadas.

1. El café de products ahora cuesta 15,90 €. ¿Qué debería mostrar el pedido 8, que es antiguo?

14,90 €, porque ese es el precio al que se compró15,90 €, porque ese es el precio de ahoraDepende de cuándo se abra el informe

Comprobar

La idea

Desnormalización

El total del pedido es diferente. De verdad se calcula a partir de las líneas. Pero la lista de pedidos de la web lo muestra para cada pedido, y en lugar de sumar las líneas cada vez, puedes guardarlo en orders:

SQLbase de datos: tienda

ALTER TABLE orders ADD COLUMN total REAL;

UPDATE orders SET total = (
  SELECT SUM(quantity * unit_price) FROM order_items
  WHERE order_items.order_id = orders.id
);

SELECT id, total FROM orders WHERE id >= 58;

ALTER TABLE orders ADD COLUMN total REAL; UPDATE orders SET total = ( SELECT SUM(quantity * unit_price) FROM order_items WHERE order_items.order_id = orders.id ); SELECT id, total FROM orders WHERE id >= 58;

▶ EjecutarRestaurar la consultaBase nueva

Una copia deliberada de datos que se podrían calcular se llama desnormalización. La haces cuando leer es mucho más frecuente que cambiar y el cálculo es caro.

Míralo

El total que miente

Un compañero añade café al pedido 60 pero se olvida de total:

SQLbase de datos: tienda

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (60, 17, 1, 14.9);

SELECT o.id, o.total, SUM(oi.quantity * oi.unit_price) AS items
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.id = 60
GROUP BY o.id, o.total;

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (60, 17, 1, 14.9); SELECT o.id, o.total, SUM(oi.quantity * oi.unit_price) AS items FROM orders o JOIN order_items oi ON oi.order_id = o.id WHERE o.id = 60 GROUP BY o.id, o.total;

▶ EjecutarRestaurar la consultaBase nueva

La web muestra 14 €, mientras las líneas suman 28,90 €. Nada en la base de datos lo impidió. El costo de la copia es que cada cambio en las líneas debe cambiar también el total: en el programa, en cada lugar que escribe en order_items, o con un trigger (el módulo «Transacciones»).

Ejercicio

Dos tareas

1. Contabilidad sospecha que algunos totales están mal. Encuentra los pedidos en que total no es igual a la suma de las líneas. Muestra el número, el total guardado y el real.

Los pedidos con un total incorrecto:

SQLbase de datos: tienda

Comprobar▶ Ejecutar

2. Corrige los totales de los dos pedidos calculándolos de nuevo a partir de las líneas.

Actualiza total de los pedidos 59 y 60:

SQLbase de datos: tienda

Comprobar▶ Ejecutar

Test

Ponte a prueba

1. ¿Cuál es una clave sustituta?

id INTEGER PRIMARY KEY, que la base de datos asigna solaEl correo del clienteUn número de identificación nacional

2. La tienda usa id como clave. ¿Debe el correo seguir siendo UNIQUE?

Sí, si no dos clientes podrían tener el mismo correoNo, ya hay una claveSolo si es NOT NULL

3. ¿Por qué unit_price en order_items no rompe la normalización?

La rompe, pero está permitidoPorque es otro hecho: el precio el día de la compra, no el de hoyPorque es un número

4. Una columna total en orders guarda la suma de las líneas. ¿Cuál es el costo?

Cada cambio en las líneas debe cambiar también total, o la suma queda malLos pedidos pasan a ser de solo lecturaNinguno, solo acelera las cosas

Comprobar

Empezar la lección de nuevo

Lecciones relacionadas

• Se basa en: Claves foráneas: vínculos entre tablas · ALTER TABLE: cambiar una tabla · Tipos de relación: 1:1, 1:N, M:N · Normalización
• Continúa en: OLTP y OLAP · Bases de datos documentales · Bases de datos columnares y series temporales · La panadería, etapa 1: la base de datos y las tablas
• Ejercicios sobre el tema: Diseñar una base de datos