Pasando de un proyecto a otro, desafortunadamente, nos enfrentamos con la falta de estándares uniformes para el diseño de bases de datos, a pesar del hecho de que SQL ha existido durante varias décadas. Sospecho que la razón se debe en parte a que la mayoría de los desarrolladores no entienden la arquitectura de la base de datos. A lo largo de los años de mi trabajo en la contratación de desarrolladores, solo me encontré varias veces con quienes podían normalizar correctamente la base de datos. Honestamente, esta puede ser una tarea difícil, pero muchos de los desarrolladores que entrevisté, incluso con fluidez en SQL, no tenían habilidades de diseño de bases de datos.

Este artículo no trata sobre la normalización de la base de datos. Si quieres aprender esto, aquí te conté brevemente lo básico.

Si tiene una base de datos en funcionamiento, debe responder a su pregunta: "¿qué estándares se pueden aplicar para facilitar el uso de esta base de datos?". Si estos estándares se usan ampliamente, entonces será fácil para usted usar la base de datos, ya que no tiene que estudiar y recordar nuevos conjuntos de estándares cada vez que comience a trabajar con una nueva base de datos.

¿Nombre o subrayado de CamelCase?

Constantemente encuentro bases de datos en las que las tablas se nombran al estilo de CustomerOrders o customer_orders . ¿Cuál es mejor usar? Quizás desee aplicar un estándar ya establecido, pero si está creando una nueva base de datos, le recomiendo usar guiones bajos para aumentar la accesibilidad. La frase "bajo valor" tiene un significado diferente en comparación con "bajo valor", pero con un guión bajo, el primero siempre estará bajo under_value y el segundo estará bajo undervalue . Y cuando usamos CamelCase obtenemos Undervalue y UnderValue , que son idénticos en términos de SQL que no distingue entre mayúsculas y minúsculas. Además, si tiene problemas de visión y está experimentando constantemente con auriculares y clavijas para enfatizar las palabras, entonces el subrayado es mucho más fácil de leer.

Finalmente, CamelCase es difícil de leer para aquellos para quienes el inglés no es nativo.
Para resumir, esta no es una recomendación estricta, sino una preferencia personal.

¿Plural o singular en los nombres de las tablas?

Los expertos en teoría de bases de datos han estado discutiendo durante mucho tiempo si las tablas deben ser singulares (cliente) o plurales (clientes). Permítanme cortar este nudo gordiano sin profundizar en la teoría, simplemente con la ayuda del pragmatismo: los nombres de tablas en plural tienen menos probabilidades de entrar en conflicto con palabras clave reservadas.

¿Tienes usuarios - users ? SQL tiene la palabra clave del user . ¿Necesitas una tabla de restricciones? constraint es una palabra reservada. La palabra audit
reservado, pero ¿necesita una tabla de audit ? Simplemente use la forma plural de sustantivos, y luego la mayoría de las palabras reservadas no le molestarán en SQL. Incluso PostgreSQL, que tiene un excelente analizador de SQL, ha tropezado con la tabla de user .

Simplemente use el plural y la probabilidad de conflicto será mucho menor.

No nombre la columna con ID como "id"

Yo mismo he pecado con los años. Una vez que trabajé con un cliente en París, y el DBA se quejó de mí cuando le id el nombre a la columna de id . Pensé que era solo un pedante. De hecho, el nombre de la columna customers.id es único, y customers.customer_id es una repetición de información.

Y luego tuve que depurar esto:

 SELECT thread.* FROM email thread JOIN email selected ON selected.id = thread.id JOIN character recipient ON recipient.id = thread.recipient_id JOIN station_area sa ON sa.id = recipient.id JOIN station st ON st.id = sa.id JOIN star origin ON origin.id = thread.id JOIN star destination ON destination.id = st.id LEFT JOIN route ON ( route.from_id = origin.id AND route.to_id = destination.id ) WHERE selected.id = ? AND ( thread.sender_id = ? OR ( thread.recipient_id = ? AND ( origin.id = destination.id OR ( route.distance IS NOT NULL AND now() >= thread.datesent + ( route.distance * interval '30 seconds' ) )))) ORDER BY datesent ASC, thread.parent_id ASC 

¿Notaste el problema? Si SQL usara nombres de identificación completos, como email_id , star_id o station_id , entonces los errores desaparecerían inmediatamente cuando escribí este código , y no más tarde, cuando traté de entender lo que hice mal.

Hágase un favor y use los nombres completos para la identificación. Gracias despues.

Nombres de columna

Dé a las columnas los nombres más descriptivos posibles. Digamos que la columna de temperature no tiene nada que ver con esto:

 SELECT name, 'too cold' FROM areas WHERE temperature < 32; 

Vivo en Francia, y para nosotros una temperatura de 32 grados será "demasiado fría". Por lo tanto, es mejor nombrar la columna fahrenheit .

 SELECT name, 'too cold' FROM areas WHERE fahrenheit < 32; 

Ahora todo está completamente claro.

Si tiene restricciones de clave externa, siempre que sea posible, asigne el mismo nombre a las columnas a ambos lados de la restricción. Aquí hay un SQL razonable perfectamente pensado:

 SELECT * FROM some_table s JOIN some_other_table o ON o.owner = s.person_id; 

Este código está realmente bien. Pero cuando observa la definición de la tabla, verá que some_other_table.owner tiene una restricción de clave externa con companies.company_id . Entonces, esencialmente este SQL está mal. Era necesario usar nombres idénticos:

 SELECT * FROM some_table s JOIN some_other_table o ON o.company_id = s.person_id; 

Ahora está claro de inmediato que tenemos un error, solo necesita verificar una línea de código y no referirse a la definición de la tabla.

Sin embargo, quiero señalar que esto no siempre se puede hacer. Si tiene una tabla con un almacén de origen y un destino, es posible que desee comparar source_id con destination_id con warehouse_id . En este caso, es mejor dar los nombres source_warehouse_id y destination_warehouse_id .

También tenga en cuenta que en el ejemplo anterior, el owner describirá el propósito mejor que company_id . Si esto le parece confuso, puede nombrar la columna owning_company_id . Entonces el nombre te dirá el propósito de la columna.

Evitar valores nulos

Este consejo es conocido por muchos desarrolladores experimentados de bases de datos, pero, desafortunadamente, no hablan de ello con la suficiente frecuencia: sin ninguna razón, no permita valores NULL en la base de datos.
Este es un tema importante pero bastante complicado. Primero, discutimos la teoría, luego su efecto en la arquitectura de la base de datos, y en conclusión analizaremos un ejemplo práctico de problemas serios causados ​​por la presencia de valores NULL.

Tipos de bases de datos

La base de datos puede contener datos de diferentes tipos : INTEGER, JSON, DATETIME, etc. El tipo está asociado con la columna y cualquier valor agregado debe corresponder a este tipo.

¿Pero qué es un tipo? Este es un nombre, un conjunto de valores válidos y un conjunto de operaciones válidas. Nos ayudan a evitar comportamientos no deseados. Por ejemplo, ¿qué sucede en Java si intenta comparar una cadena y un número?

 CustomerAccount.java:5: error: bad operand types for binary operator '>' if ( current > threshold ) { ^ first type: String second type: int 

Incluso si no notas que el current > threshold compara tipos incomparables, el compilador lo detectará por ti.

Irónicamente, las bases de datos que almacenan sus datos, y son su última línea de defensa contra la corrupción de datos, ¡funcionan terriblemente con los tipos! Solo asqueroso. Por ejemplo, si su tabla de customers tiene una clave sustituta, puede hacer esto:

 SELECT name, birthdate FROM customers WHERE customer_id > weight; 

Por supuesto, esto no tiene sentido y en realidad obtendrá un error de compilación. Muchos lenguajes de programación hacen que sea más fácil detectar tales errores de tipo, pero con las bases de datos, lo contrario es cierto.

Esta es una situación normal en el mundo de la base de datos, probablemente porque el primer estándar SQL fue lanzado en 1992 . Las computadoras eran lentas en esos años, y todo lo que complicaba la implementación indudablemente ralentizaba las bases de datos.

Y luego los valores NULL aparecen en la escena. El estándar SQL los implementó correctamente en un solo lugar, en los IS NOT NULL IS NULL y IS NOT NULL . Como el valor NULL es desconocido por definición, no puede tener operadores diseñados para él. Y entonces hay IS NULL y IS NOT NULL lugar de = NULL y != NULL . Y cualquier comparación de valores NULL conduce a la aparición de un nuevo valor NULL.

Si esto le parece extraño, será mucho más fácil si escribe "desconocido" en lugar de NULL:

La comparación de valores desconocidos NULL da como resultado valores desconocidos NULL .

Sí, ya veo!

¿Qué significa un valor nulo?

Armados con las migajas de la teoría, consideramos sus consecuencias prácticas.

Debe pagar una bonificación de $ 500 a todos los empleados cuyo salario del año ascendió a más de $ 50 mil. Escriba este código:

 SELECT employee_number, name FROM employees WHERE salary > 50000; 

Y acaba de ser despedido, porque su jefe ganó más de $ 50 mil, pero su salario no está en la base de datos (en la columna de employees.salary es NULL), y el operador de comparación no puede comparar NULL con 50 000.

¿Por qué hay NULL en esta columna? Tal vez el salario es confidencial. Tal vez la información aún no ha llegado. Tal vez este es un consultor y no se le paga. Tal vez tiene un salario por hora, no un salario. Hay muchas razones por las que pueden faltar datos.

La presencia o ausencia de información en la columna sugiere que depende de otra cosa , y no de la desnormalización de la clave primaria y la base de datos. Por lo tanto, las columnas en las que puede haber valores NULL son buenos candidatos para crear nuevas tablas. En este caso, es posible que tenga tablas de , _ , __ , etc. Todavía lo despiden por combinar a ciegas los salarios y su jefe no tiene uno. Pero entonces su base comienza a proporcionarle suficiente información para sugerir que el problema es más que un problema salarial.

Y sí, fue un ejemplo estúpido, pero fue el colmo.

Los valores NULL conducen a situaciones lógicamente imposibles.

Puede parecerle que soy pedante con respecto a los valores NULL. Sin embargo, veamos otro ejemplo que está mucho más cerca de la realidad.

Hace unos años, trabajé en Londres para un registrador de dominios e intenté entender por qué una consulta SQL de 80 líneas devuelve datos incorrectos. En esa situación, la información definitivamente debería haber sido devuelta, pero esto no sucedió. Me da vergüenza admitirlo, pero me llevó un día comprender que la razón era una combinación de condiciones:

• Usé OUTER JOIN.
  
• Podrían generar fácilmente valores NULL.
  
• Los valores NULL pueden hacer que SQL dé una respuesta incorrecta.
  

Muchos desarrolladores no conocen el último aspecto, así que veamos un ejemplo del libro Base de datos en profundidad . Un diagrama simple de dos tablas:

suppliers

parts


Es difícil encontrar un ejemplo más simple.

Este código devuelve p1 .

 SELECT part_id FROM parts; 

¿Qué hará este código?

 SELECT part_id FROM parts WHERE city = city; 

No devolverá nada, porque no puede comparar un valor NULL, incluso con otro NULL o el mismo NULL. Parece extraño porque la ciudad en cada línea debería ser la misma, incluso si no lo sabemos, ¿verdad? Entonces, ¿qué devolverá el siguiente código? Intenta entender esto antes de seguir leyendo.

 SELECT s.supplier_id, p.part_id FROM suppliers s, parts p WHERE p.city <> s.city OR p.city <> 'Paris'; 

No obtuvimos una cadena en respuesta, porque no podemos comparar la ciudad NULL ( p.city ) y, por lo tanto, ninguna de las ramas de la WHERE dará lugar a true .

Sin embargo, sabemos que la ciudad desconocida es París o no París. Si es París, entonces la primera condición será verdadera ( <> 'London' ). Si no es París, la segunda condición será verdadera ( <> 'Paris' ). Por lo tanto, la WHERE debe ser true , pero no lo es, y como resultado, SQL genera un resultado lógicamente imposible.

Fue un error que encontré en Londres. Cada vez que escribe SQL que puede generar o contener valores NULL, corre el riesgo de obtener un resultado falso. Esto sucede con poca frecuencia, pero es muy difícil de identificar.

Resumen

• Use __ lugar de CamelCase .
  
• Los nombres de las tablas deben estar en plural.
  
• Dé nombres extendidos para los campos con identificadores ( item_id lugar de id ).
  
• Evite nombres de columna ambiguos.
  
• Si es posible, nombre las columnas con claves foráneas de la misma manera que las columnas a las que se refieren.
  
• Siempre que sea posible, agregue NOT NULL a todas las definiciones de columna.
  
• Siempre que sea posible, evite escribir SQL que pueda generar valores NULL.
  

Aunque no es perfecta, esta guía de diseño de bases de datos te facilitará la vida.