Cuándo desormalizar un layout de database

Sé que normalis (z) ation ha sido ampliamente discutido en Stack Overflow. He leído muchas de las discusiones previas. Aunque tengo algunas preguntas adicionales.

Estoy trabajando en un sistema henetworkingado con al less 100 tablas. La database tiene una estructura no normalizada, tablas que contienen una variedad de datos dispares y otros problemas. Me han dado la tarea de tratar de mejorarlo. No puedo comenzar de nuevo, pero necesito modificar el esquema existente.

En el pasado, siempre he intentado diseñar bases de datos normalizadas. Ahora las preguntas. Un desarrollador senior ha sugerido que en algunos casos no podemos normalizar:

1) Con datos temporales. Por ejemplo, se crea una factura que vincula a un producto. Si un cliente solicita una copy de esta factura un año después, debemos poder presentar una copy exacta del original. ¿Qué sucede si se actualiza el precio, el nombre o la descripción del producto? El directivo sugirió que el precio y otra información del producto se copyra en la tabla de facturas. Estoy pensando que tal vez deberíamos tener otra tabla como productPrice que tenga un campo de date para que podamos rastrear los cambios en el precio a lo largo del time. Necesitaríamos lo mismo para la descripción del producto y el nombre, supongo. Parece complicado. ¿Qué piensas?

2) La database es un sistema de contabilidad. No estoy muy familiarizado con la contabilidad. Por el momento, algunos datos resumidos se derivan y almacenan en la database. Por ejemplo, las ventas totales del año. Mi asociado senior ha dicho que a los contadores les gusta comprobar que las cosas estén correctas al comparar este valor con los datos que se calculan realmente a partir de las facturas, etc., para darles la security de que la aplicación está funcionando correctamente. Dijo que en este momento, por ejemplo, podemos decir si alguien borró una factura del año pasado por error porque los totales no serán los mismos. También señaló que podría ser bastante lento calcular estos totales sobre la marcha. Por supuesto, dije que los datos no deberían duplicarse y siempre deberían calcularse cuando sea necesario. Sugerí que pudiéramos usar SQL Reporting Services u otra solución que generará estos informes de la noche a la mañana y los almacenará en caching. De todos modos él no está convencido. ¿Algún comentario sobre esto?

Muchas gracias 🙂
Aclamaciones
marca

EDITAR

Gracias por las excelentes respuestas. Es una pena que solo pueda marcar una como respuesta porque aquí hay muchas buenas sugerencias.

Su colega principal es un desarrollador, no un modelador de datos. Es mejor comenzar de cero, sin ellos. La normalización es complicada solo para aquellos que no leen libros, y obtienen su "conocimiento" de los aficionados en el wiki. Es justo que te haga pensar, pero algunos de los problemas son absurdos.

Tus numbers:

  1. Debe apreciar las diferencias entre los datos en línea reales y los datos históricos; luego, la diferencia entre las necesidades meramente históricas y de file. Todos ellos son adecuados para el requisito empresarial específico, y son incorrectos para todos los demás, no existe el bien y el mal universales.

    • ¿Por qué no hay una copy en papel de la factura? En la mayoría de los países, eso sería un requisito legal y fiscal, ¿cuál es exactamente la dificultad de extraer la factura anterior?
    • donde la database tiene el requisito de almacenar las facturas cerradas, entonces, tan pronto como se cierra la factura, necesita un método para capturar esa información.
    • ProductPrice (en realidad, lo llamaría ProductDate ) es una buena idea, pero puede que no sea necesario. Pero tiene razón, necesita evaluar la moneda de los datos, en el context completo de toda la database.
    • No veo cómo sería útil copyr el precio del producto en la tabla de facturas (¿no hay muchas líneas de pedido?)
    • en las bases de datos modernas, donde se requiere que la copy de la factura sea regurgitada, la Factura cerrada se almacena adicionalmente en una forma diferente, por ejemplo, XML. Un cliente guarda los PDF como BLOB. Así que no hay problemas con el precio del producto hace cinco años. Pero los datos básicos de la factura están en línea y son actuales, incluso para facturas cerradas; simplemente no puede volver a calcular la factura antigua con los precios actuales.
    • algunas personas usan una tabla archive_invoice, pero eso tiene problemas porque ahora cada segmento de código o herramienta de informe de usuario debe searchse en dos lugares (tenga en count que en la actualidad algunos usuarios entienden las bases de datos mejor que la mayoría de los desarrolladores)
    • De todos modos, eso es todo discusión, para su comprensión. Ninguna de las bases de datos que he escrito en 30 años ha tenido ese tipo de problema, y ​​todas cumplen con los requisitos legales y fiscales.
      • La database sirve para fines actuales y de file a partir de un set de tablas (sin tablas de "file"
      • Una vez que se crea una factura, es un documento legal, y no se puede cambiar ni eliminar (puede ser revertida o parcialmente abonada por una nueva factura, con valores negativos). Están marcados IsIssued/IsPaid/Etc
      • Products no se pueden eliminar, se pueden marcar IsObsolete
      • Hay tablas separadas para InvoiceHeader y InvoiceItem
      • InvoiceItem tiene FK para InvoiceHeader y Product
      • por muchas razones (no solo las que menciona), la fila FacturaItem contiene las NumUnits; ProductPrice; TaxAmount; ExtendedPrice NumUnits; ProductPrice; TaxAmount; ExtendedPrice NumUnits; ProductPrice; TaxAmount; ExtendedPrice . Claro, esto parece una "desnormalización", pero no lo es, porque los precios, las tasas de impuestos, etc. están sujetos a cambios. Pero más importante, el requisito legal es que podamos reproducir la factura anterior a pedido.
      • (donde se puede reproducir desde files en papel, esto no es obligatorio)
      • the InvoiceTotalAmount es una columna derivada, solo SUM() de los Facturas.
        .
  2. Eso es basura. Los sistemas de contabilidad y los contadores no "funcionan" así.

    • Si se trata de un sistema de contabilidad verdadero, entonces tendrá JournalEntries, o "doble input"; eso es lo que una count calificada debe usar (por ley).

      • Doble input no significa input duplicada; significa que cada transacción financiera (un monto) tendrá una count de origen y una count de destino a la que se aplica; entonces no hay "desnormalización" o duplicación. En una database bancaria, debido a que las transactions financieras son contra counts individuales, comúnmente se representa como dos transactions financieras (filas) separadas dentro de una transacción Db. Las restricciones de bases de datos comerciales habituales se utilizan para garantizar que haya dos "lados" en cada transacción financiera.
        .
    • Asegurar que las facturas no sean borrables es un problema aparte, relacionado con la security, etc., si alguien está paranoico con respecto a las cosas que se eliminan de su database, y su database no fue asegurada por una persona calificada, entonces tienen más y diferentes problemas que tienen nada que ver con esta pregunta. Obtenga una auditoría de security y haga lo que le digan.

    • Hay algunas personas en este sitio que piensan que wiki es un lugar donde puedes aprender algo. No lo es Es un pozo negro de "definiciones" escritas por aficionados, y las "definiciones" son cambiadas constantemente por otros aficionados. No hay una definición fija en la que pueda confiar. Así que no te preocupes por lo que dice wiki o lo que dice la gente wiki dice, en el momento en que mencionan wiki, sabes que su "conocimiento" proviene de la lectura no de la calificación; y lo que están leyendo es un pozo negro en constante cambio. Previsiblemente discutirán sobre "definiciones" porque no tienen experiencia real; la experiencia solo continuará con el trabajo

    • Una database normalizada siempre es mucho más rápida que la database no normalizada. Por lo tanto, es muy importante entender qué es la normalización y la desnormalización, y qué no lo es. El process se ve obstaculizado en gran medida cuando las personas tienen "definiciones" fluidas y amateurs, lo que provoca confusiones y "discusiones" que desgastan el time. Cuando haya definido las definiciones, puede evitar todo eso y continuar con el trabajo.

    • Las tablas de resumen son bastante normales, para ahorrar time y poder de procesamiento, para volver a calcular la información que no cambia, por ejemplo: totales de YTD para cada año, pero este año; Total de MTD para cada mes en este año pero no este mes. "Recalcular siempre" los datos es un poco tonto cuando (a) la información es muy grande y (b) no cambia. Calcule solo para el mes actual

      • En los sistemas bancarios (millones de operaciones por día), en EndOfDay, también calculamos y almacenamos Daily Total. Estos se sobrescriben durante los últimos cinco días, porque los auditores están realizando cambios y se permiten inputs de diario en las transactions financieras de los últimos 5 días.
      • los sistemas no bancarios generalmente no necesitan totales diarios
        .
    • Las tablas de resumen no son una "desnormalización" (excepto a los ojos de aquellos que acaban de enterarse de la "normalización" de su "fuente" fluida mágica y en constante cambio, o como no profesionales, que aplican reglas simples en blanco y negro a todo). De nuevo, la definición no se está argumentando aquí; simplemente no se aplica a tablas de resumen.

    • Las tablas de resumen no afectan la integridad de los datos (suponiendo, por supuesto, que los datos de los que proceden sean integrales).

    • Las tablas de resumen son una adición a la database, que no requieren tener las mismas restricciones que la database. En esencia, existen tablas de informes o tablas de depósito de datos, a diferencia de las tablas de la database.

    • No hay anomalías de actualización (que es una definición estricta) relacionadas con tablas de resumen. No puede cambiar ni eliminar una factura del año pasado. Las anomalías de actualización se aplican a los datos actuales reales desnormalizados o no normalizados.

1) Este es un file. Todo lo que contiene nunca debe actualizarse. Me gustaría ir con la sugerencia del chico mayor y hacer que la tabla de facturas sea independiente. ¿Tal vez usar un blob para la factura en sí misma que contiene el lenguaje de marcado?

2) Servicios de informes, una tabla de depósito que se activa mediante un disparador, algo que se crea mediante secuencias de commands cada vez que … todo estaría bien, creo. De hecho, es ideal para ser normalizado, pero no siempre es rápido. Tengo una buena database de atención médica que administro, que está completamente normalizada … y luego tiene una serie de tablas des-normalizadas con ecuaciones acumuladas y campos comúnmente extraídos. Casi todo se ejecuta desde ese set desnormalizado; es más rápido agregarlo a un desencadenante cuando se cargan los files que seguir teniendo que extraer de varias tablas cada vez que quiero ver un informe de 100.000 loggings.

Usted plantea puntos válidos, sin embargo, no está completamente claro sobre la normalización y lo que significa, por ejemplo, en

1) La afirmación de que mantener las facturas tal como estaban desnormalizadas es completamente errónea. Tomemos el precio, por ejemplo: si tiene un requisito comercial que establece que debe mantener el historial de precios, mantener el precio actual es incorrecto y rompe los requisitos. Y no tiene nada que ver con la normalización, simplemente no está bien diseñado. La desnormalización se trata de introducir posibilidades de ambigüedad en su model (y otros artefactos), y en este caso simplemente no está modelando correctamente su espacio problemático.
No hay nada de malo en modelar su database para soportar datos temporales (o versiones y / o separar las áreas de la database en file / temporal y el set de trabajo).

Ver la normalización sin mirar la semántica (en términos de requisitos) no es posible.

Además, si su desarrollador principal no puede ver la diferencia, entonces supongo que no obtuvo su antigüedad en el desarrollo de RDBMS;)

2) La segunda parte es de hecho desnormalización. Sin embargo, si alguna vez se encuentra con un analist senior de DB que pnetworkingica seriamente la normalización, le escuchará decir que es perfectamente aceptable que se desnormalice siempre y cuando lo haga conscientemente y se asegure de que los beneficios superen las deficiencias y que las anomalías no lo muerdan. También le indicarán que normalice el model lógico y que en el model físico se le permite desviarse del ideal para diversos fines (performance, mantenimiento, etc.). En mi libro, el objective principal de la normalización es que no tenga anomalías ocultas (consulte este artículo en 5NF, por ejemplo)

El almacenamiento en caching de resultados intermedios está permitido incluso en bases de datos normalizadas e incluso por los más grandes evangelizadores de normalización: puede hacerlo en la capa de aplicación (como algún tipo de caching) o puede hacerlo a nivel de database o puede tener un almacén de datos para tales propósitos. Estas son todas elecciones válidas y no tienen nada que ver con la normalización del model lógico.

Además, en cuanto a su contador, debe poder convencerlo de que lo que afirma no es una buena testing y desarrollar un set de testings (tal vez junto con él) que automatizarán las testings del sistema sin intervención de los usuarios y le darán mayor confianza de que su sistema está libre de errores.

Por otro lado, sé de sistemas que requieren que los usuarios ingresen información duplicada, como ingresar el número de líneas en la factura antes o después de ingresar las líneas reales, para asegurar que la input esté completa. Esta información está "duplicada" y no tiene que almacenarla si tiene un procedimiento que validará la input. Si ese procedimiento viene más tarde, se permite almacenar los datos 'desnormalizados'; de nuevo, la semántica lo justifica y se puede ver el model como normalizado. (es beneficioso entender este concepto)

EDITAR: El término "desnormalizado" en (2) no es correcto si observas la definición formal de forms normales y si consideras un layout desnormalizado si rompe cualquiera de las forms normales (para algunas personas esto es obvio y no hay de otra manera al respecto).

Sin embargo, es posible que desee acostumbrarse a la idea de que muchas personas y texts inútiles necesarios usarán el término normalización para cualquier esfuerzo que intente networkingucir la networkingundancia en la database (solo como ejemplo, encontrará artículos científicos, que no digo que deben estar en lo cierto, solo como advertencia de que es común, que los attributes derivados de la llamada den una forma de desnormalización, ver aquí ).

Si quiere referirse a autoridades más coherentes y reconocidas (una vez más, no reconocidas por todos), tal vez las palabras de CJDate puedan hacer una clara distinción:

Gran parte de la teoría del layout tiene que ver con la networkingucción de la networkingundancia; la normalización networkinguce la networkingundancia dentro de relvars, la ortogonalidad la networkinguce a través de relvars.

consultado desde Base de datos en profundidad: teoría relacional para profesionales

y en la página siguiente

del mismo modo que una falla en la normalización implica networkingundancia y puede dar lugar a ciertas anomalías, también puede ocurrir que no se adhiera a la ortogonalidad.

Por lo tanto, el término adecuado para la networkingundancia en relvars es ortogonalidad (básicamente, todas las forms normales hablan de relvar único, por lo que si se observa estrictamente la normalización, nunca se sugieren mejoras debido a dependencies entre dos relvares diferentes).

De todos modos, uno de los otros conceptos importantes cuando se considera el layout de la database también es una diferencia entre los models de bases de datos lógicas y físicas. Muchas cosas que son útiles en el nivel físico, como las tablas con subtotales o índices, no tienen cabida en el model lógico, donde intenta establecer e investigar las relaciones entre los conceptos que está tratando de modelar. Y es por eso que puedes decir que son permisibles y que no arruinan el layout.

Las líneas a veces pueden ser un poco borrosas en lo que es model lógico y qué es el model físico. Un ejemplo especialmente bueno es una tabla con subtotales. Para considerarlo parte de la implementación física e ignorarlo en el nivel lógico, debe:

  • asegúrese de que los usuarios (y las aplicaciones) no puedan actualizar la tabla de subtotal directamente de una manera que no sea coherente con su pnetworkingicado (en otras palabras, tienen un error en el procedimiento de subtotulación)
  • garantizar que los usuarios (y las aplicaciones) no puedan actualizar la tabla de la que dependen sin actualizar el subtotal (en otras palabras, que alguna aplicación no eliminará una fila de la tabla de detalles sin actualizar el total)

Si rompe cualquiera de las reglas anteriores, terminará con una database inconsistente que proporcionará hechos inconsistentes . (En tal caso, si desea diseñar formalmente un procedimiento para corregir o examinar los problemas causados, no lo consideraría solo una tabla adicional, existiría en el nivel lógico, donde no debería estar).

Además, la normalización siempre depende de la semántica y las reglas comerciales que está tratando de modelar. Por ejemplo, DBAPerformance proporciona un ejemplo en el que almacenar el TaxAmount en la tabla de transactions no es un layout desnormalizado, pero no menciona que depende del tipo de sistema que está intentando modelar (¿es eso obvio?); por ejemplo, si la transacción tiene otro atributo llamado TaxRate , generalmente se desnormalizará porque existe una dependencia funcional en un set de attributes no key (TaxAmount = Amount * TaxRate => FD: Amount, TaxRate -> TaxAmount), y uno de estos debe eliminarse o garantizarse que sea coherente.

Obviamente, podría decir, pero, si el sistema que está creando es para una empresa de auditoría, entonces puede que no tenga dependencia funcional; podría estar auditando a alguien que está usando cálculos manuales o tiene un software defectuoso o debe tener la capacidad de registrar datos incompletos y el cálculo puede ser incorrecto originalmente y como compañía de auditoría debe registrar el hecho tal como sucedió.

Entonces, la semántica (pnetworkingicados) que están determinados por requisitos influirá si cualquiera de las forms normales se rompe – influenciando las dependencies funcionales (en otras palabras, establecer dependencies funcionales correctamente es una parte muy importante del modelado cuando se esfuerza por una database normalizada).

Estoy de acuerdo con su superior sobre (1). Una fila de tabla de transactions debe capturar todo el estado en el momento de la transacción. Período. Lo que sugiere no registra los datos reales, por lo que es inadmisible. También estoy de acuerdo con (2). Cualquier cosa que desee el negocio a través de una verificación cruzada, debe implementarla. La contabilidad se basa en comprobaciones cruzadas, doble input, libros contables adicionales, etc. Debe hacerlo. Esto es tan fundamental que ni siquiera debería considerarlo como desnormalización, al igual que la implementación del requisito comercial.

1) No requiere desnormalización. Solo necesita determinar el nivel de detalle de cada cambio que necesita y persistir con la key adecuada.

2) No tiene nada que ver con la desnormalización. El almacenamiento de datos resumidos no hace que la database se desnormalice. Almacenar resultados derivados de attributes no key en la misma tabla sería un ejemplo de desnormalización, pero eso no parece ser de lo que usted está hablando aquí.

Su desarrollador principal hace puntos extremadamente válidos. He aprendido esto de la manera más difícil mediante el mantenimiento de sistemas que no desnormalizan los datos históricos.

En cierto sentido, no está agregando ninguna sobrecarga a la database. Está creando tablas de facturas a partir de datos existentes en la database. Una factura es una instantánea en el time. Desnormalizar la información que necesita para producir esa factura puede hacer que su informe sea mucho más fácil. Cuando se requiera que presente un nuevo informe y se espera que lo haga rápidamente, apreciará la des-normalización.

En términos de tener total en la database. Esto me ha salvado el trasero cuando hice un cambio en una aplicación que causaba que los numbers no se sumran de la misma manera (no tan difícil como crees). En una aplicación en vivo, los totales me dieron un lugar definitivo para volver a corregir las discrepancias. Ya he escrito sobre esto antes, puedes leerlo aquí: http://jlrand.com/?p=95

Para 1

La factura debe calcularse a partir de las ventas y los pagos. Si no tiene datos de ventas detallados, incluido precio / producto / descuento / envío / etc., comience allí.

Para 2

Escribir un sistema de contabilidad en el db desde cero es un gran proyecto. Asegúrese de que los contadores le den las reglas comerciales para que pueda medir la precisión de su sistema. Lo último que desea es que el CFO entre en la reunión del DBA y anuncie que el DB le está cobrando de más al cliente, y lo que es peor, está subestimando e impulsando el cierre de la empresa.

Si tienes SQL Server, dale un vistazo a Adventure Works db. Si odias MS, mira Adventure Works y no lo hagas de esa manera.

La normalización de la database elimina los duplicates y hace que las consultas SQL para la actualización de datos sean más eficientes (y ofrece algunas otras mejoras).

Pero si la mayoría de sus consultas se utilizan para seleccionar datos y seleccionar consultas, conéctese a varias tablas al mismo time, puede considerar la desnormalización de estas tablas. Aumentará la cantidad de espacio en disco necesario para los datos, la ejecución del time de las consultas de actualización SQL, pero mejorará las consultas de selección.

Parece que si está considerando más bien o no debería crear un depósito de datos. Nunca debe desnormalizar su database con fines de informes históricos. Creating an archive and storing your information into your data warehouse will do both: denormalize most of the information and maintain your data history.