mysql - tutorial - the django project

base de datos de diseño relacionada con el atributo de tiempo (5)

Quiero diseñar una base de datos que se describa de la siguiente manera: cada producto tiene solo un estado en un punto de tiempo. Sin embargo, el estado de un producto puede cambiar durante su tiempo de vida. ¿Cómo podría diseñar la relación entre el producto y el estado que puede consultarse fácilmente con todos los productos de un estado específico en el momento actual? Además, ¿podría alguien darme algunos detalles en profundidad sobre la base de datos de diseño que se relacionan con la duración del tiempo como problema anterior? Gracias por cualquier ayuda

"Además, ¿alguien podría por favor darme algunos detalles en profundidad sobre la base de datos de diseño que se relaciona con la duración del tiempo como el problema anterior?"

Bueno, existe un libro de 400 páginas titulado "Datos temporales y el modelo relacional" que aborda su problema.

Ese libro también aborda numerosos problemas que los otros respondedores no han abordado en sus respuestas, por falta de tiempo o por falta de espacio o por falta de conocimiento.

La introducción del libro también declara explícitamente que "este libro no trata acerca de la tecnología que (comercialmente) está disponible para cualquier usuario hoy en día".

Todo lo que puedo observar es que los usuarios que desean funciones temporales de los sistemas SQL son, para decirlo simple, deficientes.

Incluso si esas 400 páginas pudieran "comprimirse un poco", espero que no esperen que les proporcione un resumen de todo el contenido significativo en unos pocos párrafos aquí en SO ...

Google "bases de datos bi-temporales" y "dimensiones que cambian lentamente".

Estos son dos nombres para esencialmente el mismo patrón.

Debe agregar dos columnas de marca de tiempo a su tabla de productos "VALID_FROM" y "VALID_TO".

Cuando cambie el estado de su producto, agregue una NUEVA fila con "VALID_FROM" de ahora () algún otro dato / hora efectivo conocido y configure el "VALID_TO" a 9999-12-31 23:59:59 o cualquier otra fecha ridículamente lejana en el futuro. También debe borrar la fecha "9999-12-31 ..." en la fila anterior al "VALID_FROM" actual - 1 microsegundo.

A continuación, puede consultar fácilmente el estado del producto en cualquier momento.

tablas similares a estas:

product ----------- product_id status_id name status ----------- status_id name product_history --------------- product_id status_id status_time

a continuación, escriba un activador en el producto para registrar el estado y la marca de tiempo (sysdate) en cada actualización donde cambie el estado

Aquí hay un modelo para lograr su requisito establecido.

Enlace al modelo de datos de la serie de tiempo

Enlace a la notación IDEF1X para aquellos que no están familiarizados con el estándar de modelado relacional.

Normalizado a 5NF; no hay columnas duplicadas; sin anomalías de actualización, sin nulos.
Cuando cambia el estado de un producto, simplemente inserte una fila en ProductStatus, con el DateTime actual. No es necesario tocar las filas anteriores (que fueron verdaderas y siguen siendo verdaderas). No hay valores ficticios que las herramientas de informe (que no sean su aplicación) tengan que interpretar.
DateTime es el Fecha y hora real en que el Producto se colocó en ese Estado; el "De", si quieres. El "Para" se deriva fácilmente: es el DateTime de la fila siguiente (DateTime> "From") para el Producto; donde no existe, el valor es el DateTime actual (use ISNULL).

El primer modelo está completo; (ProductId, DateTime) es suficiente para proporcionar unicidad para la clave principal. Sin embargo, dado que solicita velocidad para ciertas condiciones de consulta, podemos mejorar el modelo en el nivel físico y proporcionar:

Un índice (ya tenemos el índice PK, por lo que lo mejoraremos primero, antes de agregar un segundo índice) para admitir consultas cubiertas (los basados en cualquier disposición de {ProductId | DateTime | Status} pueden ser suministrados por el índice, sin tener para ir a las filas de datos). Que cambia la relación Status :: ProductStatus de Non-Identifying (línea discontinua) a Tipo de identificación (línea continua).
La disposición de PK se elige sobre la base de que la mayoría de las consultas serán Series temporales, basadas en Product⇢DateTime⇢Status.
El segundo índice se proporciona para mejorar la velocidad de las consultas en función del estado.
En el Arreglo Alternativo, eso se invierte; es decir, en general deseamos el estado actual de todos los productos.
En todas las representaciones de ProductStatus, la columna DateTime en el índice secundario (no el PK) es DESCending; el más reciente es primero arriba.

He proporcionado la discusión que solicitó. Por supuesto, debe experimentar con un conjunto de datos de tamaño razonable y tomar sus propias decisiones. Si hay algo aquí que no entiende, por favor pregunte, y lo ampliaré.

Respuestas a los Comentarios

Reportar todos los productos con estado actual de 2

SELECT ProductId, Description FROM Product p, ProductStatus ps WHERE p.ProductId = ps.ProductId -- Join AND StatusCode = 2 -- Request AND DateTime = ( -- Current Status on the left ... SELECT MAX(DateTime) -- Current Status row for outer Product FROM ProductStatus ps_inner WHERE p.ProductId = ps_inner.ProductId )

ProductId está indexado, col principal, ambos lados
DateTime en Indexed, 2nd col en la opción de consulta cubierta
StatusCode Indexado, 3er col en la opción de consulta cubierta
Como StatusCode en el índice es DESCending, solo se requiere una búsqueda para satisfacer la consulta interna
las filas son requeridas al mismo tiempo, para una consulta; están muy juntos (debido al Índice Clstered); casi siempre en la misma página debido al tamaño de fila corto.

Esto es SQL ordinario, una subconsulta, que usa el poder del motor SQL, procesamiento de conjunto relacional. Es el método correcto , no hay nada más rápido, y cualquier otro método sería más lento. Cualquier herramienta de informe producirá este código con unos pocos clics, sin tipear.

Dos fechas en ProductStatus

Las columnas como DateTimeFrom y DateTimeTo son errores graves. Veámoslo en orden de importancia.

Es un error bruto de Normalización. "DateTimeTo" se deriva fácilmente del único DateTime de la siguiente fila; por lo tanto, es redundante, una columna duplicada.
- La precisión no entra: se resuelve fácilmente en virtud del DataType (DATE, DATETIME, SMALLDATETIME). Ya sea que muestre uno menos segundo, microsegundo o nanosegundo, es una decisión comercial; no tiene nada que ver con los datos que se almacenan.
Implementar una columna DateTo es un 100% duplicado (de DateTime de la siguiente fila). Esto requiere el doble de espacio en el disco . Para una mesa grande, sería un desperdicio innecesario.
Dado que es una fila corta, necesitará el doble de E / S lógicas y físicas para leer la tabla, en cada acceso.
Y el doble de espacio en la caché (o dicho de otra manera, solo la mitad de las filas encajarían en un espacio de caché dado).
Al introducir una columna duplicada, ha introducido la posibilidad de error (el valor ahora se puede derivar de dos maneras: desde la columna DateTimeTo duplicada o DateTimeFrom de la fila siguiente).
Esto también es una Anomalía de actualización . Cuando actualiza cualquier DateTimeFrom se actualiza, el DateTimeTo de la fila anterior tiene que ser recuperado (no es gran cosa, ya que está cerca) y actualizado (gran cosa, ya que es un verbo adicional que puede evitarse).
"Shorter" y "atajos de codificación" son irrelevantes, SQL es un lenguaje engorroso de manipulación de datos, pero SQL es todo lo que tenemos (Just Deal With It). Cualquiera que no pueda codificar una subconsulta realmente no debería codificar. Cualquiera que duplique una columna para facilitar la "dificultad" de codificación menor realmente no debería estar modelando bases de datos.

Tenga en cuenta que si se mantiene la regla de orden superior (Normalización), se elimina todo el conjunto de problemas de orden inferior.

Piense en términos de conjuntos

Cualquiera que tenga "dificultad" o que experimente "dolor" al escribir SQL simple queda paralizado al realizar su función de trabajo. Por lo general, el desarrollador no está pensando en términos de conjuntos y la Base de datos relacional está orientada a conjuntos .
Para la consulta anterior, necesitamos el DateTime actual; dado que ProductStatus es un conjunto de Estados de producto en orden cronológico, simplemente necesitamos el último, o MAX (DateTime) del conjunto perteneciente al Producto.
Ahora veamos algo supuestamente "difícil", en términos de conjuntos . Para un informe de la duración de cada Producto en un Estado particular: DateTimeFrom es una columna disponible, y define el corte horizontal, un subconjunto (podemos excluir filas anteriores); el DateTimeTo es el primer conjunto secundario de Estados de producto.

SELECT ProductId, Description, [DateFrom] = DateTime, [DateTo] = ( SELECT MIN(DateTime) -- earliest in subset FROM ProductStatus ps_inner WHERE p.ProductId = ps_inner.ProductId -- our Product AND ps_inner.DateTime > ps.DateTime -- defines subset, cutoff ) FROM Product p, ProductStatus ps WHERE p.ProductId = ps.ProductId AND StatusCode = 2 -- Request

Pensando en términos de obtener la próxima fila está orientado a las filas, no al procesamiento orientado a conjuntos. Paralizante, cuando se trabaja con una base de datos orientada a conjuntos. Deje que el Optimizador haga todo eso pensando para usted. Comprueba tu SHOWPLAN, esto se optimiza maravillosamente.
La incapacidad de pensar en conjuntos , por lo tanto, se limita a escribir solo consultas de un solo nivel, no es una justificación razonable para: la implementación de la duplicación masiva y Anomalías de actualización en la base de datos; desperdicio de recursos en línea y espacio en disco; garantizando la mitad del rendimiento Mucho más barato aprender a escribir subconsultas SQL simples para obtener datos fácilmente derivados.