sql - quitar - Cuándo desormalizar un diseño de base de datos

modificar columna auto_increment sql server (9)

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 heredado con al menos 100 tablas. La base de datos 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 copia de esta factura un año después, debemos poder presentar una copia 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 copiara en la tabla de facturas. Estoy pensando que tal vez deberíamos tener otra tabla como productPrice que tenga un campo de fecha para que podamos rastrear los cambios en el precio a lo largo del tiempo. Necesitaríamos lo mismo para la descripción del producto y el nombre, supongo. Parece complicado. ¿Qué piensas?

2) La base de datos es un sistema de contabilidad. No estoy muy familiarizado con la contabilidad. Por el momento, algunos datos resumidos se derivan y almacenan en la base de datos. 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 seguridad 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 que 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 caché. 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.

1) Este es un archivo. 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í que contiene el lenguaje de marcado?

2) Servicios de informes, una tabla de depósito que se activa mediante un disparador, algo que se genera mediante secuencias de comandos cada vez que ... todo estaría bien, creo. De hecho, es ideal para ser normalizado, pero no siempre es rápido. Tengo una buena base de datos 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 conjunto desnormalizado; es más rápido agregarlo a un desencadenante cuando se cargan los archivos que seguir teniendo que extraer de varias tablas cada vez que quiero ver un informe de 100.000 registros.

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

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

Estoy de acuerdo con su superior sobre (1). Una fila de tabla de transacciones 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 entrada, 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.

La normalización de la base de datos elimina los duplicados 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 tiempo, puede considerar la desnormalización de estas tablas. Aumentará la cantidad de espacio en disco necesario para los datos, la ejecución del tiempo de las consultas de actualización SQL, pero mejorará las consultas de selección.

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.

Parece que si está considerando más bien o no debería crear un depósito de datos. Nunca debe desnormalizar su base de datos con fines de informes históricos. Crear un archivo y almacenar su información en su almacén de datos hará las dos cosas: desnormalizar la mayoría de la información y mantener su historial de datos.

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 números:

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 archivo. Todos ellos son correctos para el requisito comercial específico, y son incorrectos para todos los demás, no existe el bien y el mal universales.
- ¿Por qué no hay una copia 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 base de datos 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 contexto completo de toda la base de datos.
- No veo cómo sería útil copiar 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 copia 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 buscarse en dos lugares (tenga en cuenta 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 base de datos sirve para fines actuales y de archivo a partir de un conjunto de tablas (sin tablas de "archivo"
  - Una vez que se crea una factura, es un documento legal y no se puede modificar 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 archivos en papel, esto no es obligatorio)
  - the InvoiceTotalAmount es una columna derivada, solo SUM() de los Facturas.
    .
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 entrada"; eso es lo que una cuenta calificada debe usar (por ley).
  - Doble entrada no significa entrada duplicada; significa que cada transacción financiera (un monto) tendrá una cuenta de origen y una cuenta de destino a la que se aplica; entonces no hay "desnormalización" o duplicación. En una base de datos bancaria, debido a que las transacciones financieras son contra cuentas individuales, comúnmente se representa como dos transacciones 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 seguridad, etc., si alguien está paranoico con respecto a las cosas que se eliminan de su base de datos, y su base de datos 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 seguridad 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 base de datos normalizada siempre es mucho más rápida que la base de datos no normalizada. Por lo tanto, es muy importante entender qué es la normalización y la desnormalización, y qué no lo es. El proceso se ve obstaculizado en gran medida cuando las personas tienen "definiciones" fluidas y amateurs, lo que provoca confusiones y "discusiones" que desgastan el tiempo. Cuando haya definido las definiciones, puede evitar todo eso y continuar con el trabajo.
- Las tablas de resumen son bastante normales, para ahorrar tiempo 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 transacciones 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 entradas de diario en las transacciones 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 base de datos, que no requieren tener las mismas restricciones que la base de datos. En esencia, existen tablas de informes o tablas de depósito de datos, a diferencia de las tablas de la base de datos.
- 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.

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 base de datos. Está creando tablas de facturas a partir de datos existentes en la base de datos. Una factura es una instantánea en el tiempo. 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 base de datos. Esto me ha salvado antes cuando hice un cambio en una aplicación que causaba que los números no se sumaran 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

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 modelo (y otros artefactos), y en este caso simplemente no está modelando correctamente su espacio problemático.
No hay nada de malo en modelar su base de datos para soportar datos temporales (o versiones y / o separar las áreas de la base de datos en archivo / temporal y el conjunto 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 analista senior de DB que predica 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 modelo lógico y que en el modelo físico se le permite desviarse del ideal para diversos fines (rendimiento, mantenimiento, etc.). En mi libro, el objetivo principal de la normalización es que no tenga anomalías ocultas (consulte este artículo en 5NF por ejemplo)

El almacenamiento en caché 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 caché) o puede hacerlo a nivel de base de datos 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 modelo lógico.

Además, en cuanto a su contador, debe poder convencerlo de que lo que afirma no es una buena prueba y desarrollar un conjunto de pruebas (tal vez junto con él) que automatizarán las pruebas 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 entrada esté completa. Esta información está "duplicada" y no tiene que almacenarla si tiene un procedimiento que validará la entrada. 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 modelo como normalizado. (es beneficioso entender este concepto)

EDITAR: El término "desnormalizado" en (2) no es correcto si observas la definición formal de formas normales y si consideras un diseño desnormalizado si rompe cualquiera de las formas 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 textos inútiles necesarios usarán el término normalización para cualquier esfuerzo que intente reducir la redundancia en la base de datos (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 atributos derivados de la llamada den una forma de desnormalización, ver here ).

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 diseño tiene que ver con la reducción de la redundancia; la normalización reduce la redundancia dentro de relvars, la ortogonalidad la reduce 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 redundancia 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 redundancia en relvars es ortogonalidad (básicamente, todas las formas normales hablan de relvar único, por lo que si se observa estrictamente la normalización, nunca se sugieren mejoras debido a dependencias entre dos relvares diferentes).

De todos modos, uno de los otros conceptos importantes al considerar el diseño de la base de datos también es una diferencia entre los modelos de bases de datos lógicos y físicos. Muchas cosas que son útiles en el nivel físico, como las tablas con subtotales o índices, no tienen cabida en el modelo 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 diseño.

Las líneas a veces pueden ser un poco borrosas en lo que es modelo lógico y qué es el modelo 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 predicado (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 base de datos 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 transacciones no es un diseño 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 conjunto de atributos no clave (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 (predicados) que están determinados por requisitos influirá si cualquiera de las formas normales se rompe - influyendo en las dependencias funcionales (en otras palabras, establecer correctamente las dependencias funcionales es una parte muy importante del modelado cuando se esfuerza por una base de datos normalizada).