sql - relacionales - etapas del diseño de bases de datos
¿Qué tan lejos tomar la normalización en el diseño de la base de datos? (13)
Tengo estas tablas:
Projects(projectID, CreatedByID)
Employees(empID,depID)
Departments(depID,OfficeID)
Offices(officeID)
("CreatedByID" es una clave externa para empleados).
Y tengo una consulta que necesito ejecutar para casi todas las solicitudes de una aplicación web que atrapa todos los proyectos en una oficina. ¿Es una mala práctica agregar una columna redundante de "OfficeID" a los proyectos para eliminar las tres uniones? ¿O debería hacer lo siguiente?
SELECT *
FROM Projects P
JOIN Employees E ON P.CreatedBY = E.EmpID
JOIN Departments D on E.DepID = D.DepID
JOIN Offices O on D.officeID = O.officeID
WHERE O.officeID = @SomeOfficeID
Hasta que note problemas de rendimiento?
En la programación de aplicaciones, siempre sigo la regla "Escribir primero con mejores prácticas y luego optimizar", pero cuando se trata de diseño y normalización de bases de datos como este me preocupo porque los administradores de bases de datos siempre están advirtiendo sobre el costo de las uniones.
El costo de las uniones no debería preocuparte demasiado en sí mismo (a menos que intentes escalar a millones de usuarios, en cuyo caso debes preocuparte).
Me preocuparía más el efecto sobre el código que está llamando esto. Las bases de datos normalizadas son mucho más fáciles de programar, y casi siempre conducen a una mejor eficacia dentro de la propia aplicación.
Dicho esto, no se normalice más allá de los límites de la razón. He visto la normalización por el bien de la normalización, que generalmente termina en una base de datos que tiene una o dos tablas de datos reales, y 20 tablas rellenas con nada más que claves externas. Eso es claramente excesivo. La regla que normalmente uso es: si los datos en una columna se duplicarían, debería normalizarse.
En el ejemplo dado, los índices configurados correctamente en las tablas deben permitir que las uniones ocurran extremadamente rápido y se escalarán bien a los 100,000 de filas. Este suele ser el enfoque que tomo para evitar el problema.
Sin embargo, hay momentos en que los datos se escriben una vez y se seleccionan por el resto de su vida en los que realmente no tiene sentido hacer una docena de combinaciones cada vez.
Los DBA deberían preocuparse si tu db no está normalizado correctamente para empezar. Después de medir cuidadosamente el rendimiento y determinar que tiene cuellos de botella puede comenzar a desnormalizar, pero sería extremadamente cauteloso.
Me preocuparían más los DBA que le advierten sobre el costo de las uniones, a menos que se encuentre en una situación altamente patológica.
No deberías mirar la desnormalización antes de haber intentado todo lo demás.
¿El rendimiento de esto es realmente un problema? ¿Su base de datos tiene alguna característica que pueda usar para acelerar las cosas sin comprometer la integridad? ¿Puedes aumentar tu rendimiento mediante el almacenamiento en caché?
Normalice para modelar los conceptos en su diseño y su relación. Piense en qué relaciones pueden cambiar y qué cambio significará en términos de su diseño.
En el esquema que publicaste, hay lo que a mí me parece un error evidente (que puede no ser un error si tienes un caso especial en términos de cómo funciona tu organización): hay una suposición implícita de que cada departamento se encuentra exactamente una oficina, y que todos los empleados que están en el mismo departamento trabajen en esa oficina.
¿Qué pasa si el departamento ocupa dos oficinas?
¿Qué pasa si un empleado nominalmente pertenece a un departamento, pero trabaja en una oficina diferente (suponiendo que se refiera a oficinas físicas)?
Normaliza hasta que duela, luego desnormaliza hasta que funcione
Si usa números enteros (o BIGINT) como ID y son la clave primaria agrupada, debería estar bien.
Aunque parece que siempre será más rápido encontrar una oficina de un proyecto, ya que siempre está buscando claves principales, el uso de índices en las claves externas hará que la diferencia sea mínima ya que los índices también cubrirán las claves principales.
Si alguna vez encuentra una necesidad para desnormalizar los datos, puede crear una tabla de caché en un cronograma o activador.
Siempre normalice todo lo necesario para eliminar problemas de integridad de la base de datos (es decir, posibles datos duplicados o faltantes).
Incluso si hubo mejoras de rendimiento debido a la desnormalización (que generalmente no es el caso), el precio de perder la integridad de los datos es demasiado alto para justificarlo.
Simplemente pregúntele a cualquiera que haya tenido que trabajar para solucionar todos los problemas poco conocidos de una base de datos heredada, si preferirían buenos datos o incrementos de velocidad insignificantes (si los hubiera).
Además, como menciona John, si finalmente necesita datos desnormalizados (velocidad / informes / etc.), créelo en una tabla separada, conservando los datos brutos.
No desnormalizar
Diseñe sus tablas de acuerdo con principios de diseño simples y sólidos que harán que sea fácil implementar el resto de su sistema. Fácil de construir, poblar, usar y administrar la base de datos. Fácil y rápido para ejecutar consultas y actualizaciones en contra. Fácil de revisar y extender el diseño de la mesa cuando la situación lo requiera, e innecesario por razones livianas y transitorias.
Un conjunto de principios de diseño es la normalización. La normalización genera tablas que son fáciles y rápidas de actualizar (incluidas las inserciones y eliminaciones). La normalización evita las anomalías de actualización y evita la posibilidad de una base de datos que se contradice a sí misma. Esto evita una gran cantidad de errores al hacerlos imposibles. También evita una gran cantidad de cuellos de botella de actualización haciéndolos innecesarios. Esto es bueno.
Hay otros conjuntos de principios de diseño. Conducen a diseños de mesa que están menos que completamente normalizados. Pero eso no es "desnormalización". Es solo un diseño diferente, algo incompatible con la normalización.
Un conjunto de principios de diseño que conduce a un diseño radicalmente diferente de la normalización es el diseño del esquema en estrella. El esquema de estrella es muy rápido para consultas. Incluso se pueden realizar uniones y agregaciones a gran escala en un tiempo razonable, dado un buen DBMS, buen diseño físico y suficiente hardware para realizar el trabajo. Como era de esperar, un esquema de estrella sufre anomalías de actualización. Debe programar estas anomalías cuando mantiene la base de datos actualizada. En general, necesitará un proceso ETL cuidadosamente controlado y cuidadosamente creado que actualice el esquema en estrella desde otras fuentes de datos (tal vez normalizadas).
Usar datos almacenados en un esquema de estrella es dramáticamente fácil. Es tan fácil que al usar algún tipo de OLAP y motor de informes, puede obtener toda la información necesaria sin escribir ningún código y sin sacrificar demasiado el rendimiento.
Se necesita un análisis de datos bueno y algo profundo para diseñar un buen esquema normalizado. Los errores y omisiones en el análisis de datos pueden dar como resultado dependencias funcionales no descubiertas. Estos FD no descubiertos resultarán en desvíos involuntarios de la normalización.
También se necesita un análisis de datos bueno y algo profundo para diseñar y construir un buen esquema de estrella. Los errores y las omisiones en el análisis de datos pueden dar como resultado elecciones desafortunadas en dimensiones y granularidad. Esto hará que ETL sea casi imposible de construir, y / o hará que la capacidad de carga de información de la estrella sea inadecuada para las necesidades emergentes.
Un análisis de datos bueno y algo profundo no debería ser una excusa para la parálisis del análisis. El análisis debe ser correcto y razonablemente completo en un corto período de tiempo. Más corto para proyectos más pequeños. El diseño y la implementación deberían poder sobrevivir algunas adiciones tardías y correcciones al análisis de datos y a los requisitos, pero no a un torrente constante de revisiones de requisitos.
Esta respuesta expande su pregunta original, pero creo que es relevante para el diseñador de bases de datos.
Es mejor mantener ese esquema en Tercera Forma Normal y dejar que su DBA se queje sobre el costo de las uniones.
La desnormalización tiene la ventaja de rápidos SELECT s en consultas grandes.
Las desventajas son:
Se necesita más codificación y tiempo para garantizar la integridad (que es lo más importante en su caso)
Es más lento en DML (INSERT / UPDATE / DELETE)
Se necesita más espacio
En cuanto a la optimización, puede optimizar para una consulta más rápida o para un DML más rápido (como regla, estos dos son antagonistas).
Optimizar para una consulta más rápida a menudo implica duplicar datos, ya sea desnormalización, índices, tablas adicionales de lo que sea.
En el caso de los índices, el RDBMS lo hace por usted, pero en caso de desnormalización, deberá codificarlo usted mismo. ¿Qué pasa si el Department muda a otra Office ? Tendrás que arreglarlo en tres tablas en lugar de una.
Entonces, como puedo ver en los nombres de sus tablas, no habrá millones de registros allí. Entonces será mejor que normalices tus datos, será más simple de administrar.
Normalización: es una decisión de calidad.
Desnormalización: es una decisión de rendimiento.
Es por eso que se dice -
Normaliza hasta que duela, Desnormaliza hasta que funcione.
Las siguientes decisiones de calidad indican cuál es la forma menos normal con la que puede vivir:
- ¿Cuánta no redundancia es importante para sus tablas?
- ¿Qué tan rápido desea la administración de datos?
- ¿Qué tan clara quieres la relación entre tus tablas?
Las siguientes decisiones de rendimiento indican cuál es la forma normal más alta aceptable para sus clientes / clientes / aplicación :
- ¿La respuesta de mi base de datos es lo suficientemente rápida?
- ¿Hay demasiadas uniones que causan una desaceleración?
Después de haber arreglado la Forma Normal mínima y más alta aceptable en su caso, elija la Forma Normal en cualquier punto intermedio.