Un cliente, cuarenta direcciones
Una pequeña empresa guarda todos los pedidos en una hoja grande. Cada línea repite el nombre y la dirección del cliente.
Un cliente se muda. Alguien corrige la dirección en treinta y ocho de sus cuarenta líneas y se deja dos.
Ahora la empresa tiene dos direcciones para una persona y no sabe cuál es la buena.
Al terminar esta lección
Nota
Puedo partir una tabla plana en un diseño que evita la repetición.
Palabras clave
| Palabra | Qué significa |
|---|---|
| Redundancia | el mismo dato guardado en más de un sitio |
| Anomalía | un fallo que causa la redundancia al cambiar los datos |
| Dependencia | una columna cuyo valor lo fija otra columna |
| Atómico | que guarda un solo valor, nunca una lista |
| Forma normal | cada nivel de un diseño ordenado, de la primera a la tercera |
| Normalización | partir tablas hasta que desaparece la redundancia |
Las celdas que rellenan los estudiantes quedan en blanco en esta vista previa.
La repetición causa tres fallos
En una tabla plana el mismo dato se escribe muchas veces. Eso causa tres fallos, llamados anomalías.
Una anomalía de actualización: cambias una copia y te dejas las demás.
Una anomalía de inserción: no puedes anotar un cliente hasta que haga un pedido.
Una anomalía de borrado: al borrar el último pedido, se pierde el único registro del cliente.
Ejemplo resuelto: una tabla plana
| order_id | customer | city | country | product | qty |
|---|---|---|---|---|---|
| 1 | Ana | Madrid | España | ratón | 2 |
| 1 | Ana | Madrid | España | teclado | 1 |
| 2 | Beto | Lisboa | Portugal | ratón | 5 |
| 3 | Ana | Madrid | España | cable | 1 |
Las celdas que rellenan los estudiantes quedan en blanco en esta vista previa.
Mira lo que ahorra un diseño
La base de la tienda ya está partida en tablas. Vas a armar la vista plana de un cliente y a contar lo que repetiría.
Ejecuta primero las dos líneas de preparación.
Aceptar el certificado del servidor
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfSe muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
Un nombre corto para la tienda
alias sql='mariadb -h db -u student -pstudent shop -t -e'Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
Un cliente como tabla plana
sql "SELECT customers.first_name, customers.city, orders.id AS order_no, products.name AS product FROM customers JOIN orders ON orders.customer_id = customers.id JOIN order_items ON order_items.order_id = orders.id JOIN products ON products.id = order_items.product_id WHERE customers.id = 33 ORDER BY orders.id, products.name LIMIT 5"Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
Líneas frente a clientes
sql "SELECT COUNT(*) AS flat_rows, COUNT(DISTINCT orders.customer_id) AS customers FROM orders JOIN order_items ON order_items.order_id = orders.id"Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
Primera y segunda forma normal
Primera forma normal (1FN): cada celda guarda un valor. Nada de listas como "ratón, teclado" en una celda, ni columnas item1, item2, item3.
Segunda forma normal (2FN): cada columna depende de toda la clave. Si la clave es order_id más product, una columna sobre el cliente depende solo de order_id. Sácala a otra tabla.
Ejemplo resuelto: partir la tabla plana
| Tabla nueva | Columnas | Clave | Guarda |
|---|---|---|---|
| orders | order_id, customer_id | order_id | Una fila por pedido |
| order_items | order_id, product_id, qty | order_id y product_id | Una fila por línea |
| customers | customer_id, name, city_id | customer_id | Una fila por persona |
| cities | city_id, city, country | city_id | Una fila por ciudad |
Las celdas que rellenan los estudiantes quedan en blanco en esta vista previa.
La tercera forma quita una dependencia oculta
Tercera forma normal (3FN): ninguna columna depende de otra que no sea la clave.
El id de un cliente fija su city. La city fija el country. Así, el país depende de la clave solo a través de la ciudad. Es una dependencia transitiva, oculta a primera vista.
Lleva city y country a su propia tabla, y el cliente guarda solo un número de ciudad.
¿Tiene alguna ciudad dos países?
sql "SELECT COUNT(*) AS cities_with_two_countries FROM (SELECT city FROM customers GROUP BY city HAVING COUNT(DISTINCT country) > 1) AS x"Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
Ciudades frente a países
sql "SELECT COUNT(DISTINCT city) AS cities, COUNT(DISTINCT country) AS countries FROM customers"Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
Las ciudades que más se repiten
sql "SELECT city, country, COUNT(*) AS customers FROM customers GROUP BY city, country ORDER BY customers DESC, city LIMIT 3"Se muestra aquí y no se ejecuta. Crea una cuenta gratuita para pulsar Ejecutar y escribirlo en el terminal de tu propio laboratorio.
Un error frecuente
Advertencia
Mucha gente cree que cuantas más tablas, mejor. Partir quita repetición, pero cada partición exige un join para volver a leer los datos. Para en la tercera forma normal, salvo que tengas una razón medida para seguir.
Pruébalo: cada cliente una vez
-- Los clientes, escritos una vez cada uno.
SELECT DISTINCT customer, city
FROM flat_orders
ORDER BY customer;
-- Cámbialo para listar cada ciudad con su país, una vez.
Para ir más allá
Crea las cuatro tablas del ejemplo resuelto en tu propia base, student_depot. Añade también una pequeña tabla products, porque order_items apunta a una. Da a cada tabla su clave primaria, y una clave foránea allí donde una tabla apunte a otra.
Después carga en ellas las seis filas de la tabla plana de la página. Comprueba tu diseño con el join que vuelve a imprimir la tabla plana.