database - rutina - Diseñar un esquema de ''Orden'' en el que hay tablas de definición de producto dispares

rutina definicion 6 dias

Question

Este es un escenario que he visto en múltiples lugares a lo largo de los años; Me pregunto si alguien más se encontró con una solución mejor que yo ...

Mi empresa vende una cantidad relativamente pequeña de productos, sin embargo, los productos que vendemos son altamente especializados (es decir, para seleccionar un producto determinado, debe proporcionarse una cantidad importante de detalles al respecto). El problema es que si bien la cantidad de detalles requeridos para elegir un producto dado es relativamente constante, los tipos de detalles requeridos varían mucho entre los productos. Por ejemplo:

El producto X podría tener características de identificación como (hipotéticamente)

• ''Color'',
• ''Material''
• ''Tiempo medio para fallar''

pero el Producto Y podría tener características

• ''Espesor'',
• ''Diámetro''
• ''Fuente de alimentación''

El problema (uno de ellos, de todos modos) al crear un sistema de pedido que utiliza tanto Producto X como Producto Y es que una Línea de pedido debe referirse, en algún momento, a lo que "vende". Dado que el Producto X y el Producto Y se definen en dos tablas diferentes, y la desnormalización de productos utilizando un esquema de tabla amplia no es una opción (las definiciones del producto son bastante profundas), es difícil ver una manera clara de definir la Línea de pedido en tal forma en que la entrada, edición e informes de órdenes son prácticas.

Cosas que he probado en el pasado

• Cree una tabla principal llamada ''Producto'' con columnas comunes al Producto X y Producto Y, luego use ''Producto'' como referencia para la tabla OrderLine, y cree una relación FK con ''Producto'' como el lado primario entre las tablas para el Producto X y el Producto Y. Esto básicamente coloca a la tabla ''Producto'' como la matriz de OrderLine y de todas las tablas de productos dispares (por ejemplo, Productos X e Y). Funciona bien para la entrada de pedidos, pero causa problemas con el informe o la edición de pedidos ya que el registro ''Producto'' debe rastrear qué tipo de producto es para determinar cómo unir ''Producto'' a su hijo más detallado, Producto X o Producto Y. Ventajas : las relaciones clave se conservan. Desventajas : informes, edición en la línea de pedido / nivel de producto.
• Cree las columnas ''Tipo de producto'' y ''Clave del producto'' en el nivel de la Línea de pedido, luego use alguna lógica o vistas CASE para determinar el producto personalizado al que se refiere la línea. Esto es similar al artículo (1), sin la tabla común de ''Producto''. Considero que es una solución más "rápida y sucia", ya que elimina por completo las claves externas entre las líneas de pedido y sus definiciones de productos. Ventajas : solución rápida. Desventajas : igual que el ítem (1), más pérdida de RI.
• Homogeneice las definiciones del producto creando una tabla de encabezado común y utilizando pares clave / valor para los atributos personalizados (OrderLine [n] <- [1] Product [1] <- [n] ProductAttribute). Ventajas : las relaciones clave se conservan; sin ambigüedad sobre la definición del producto. Desventajas : informes (recuperación de una lista de productos con sus atributos, por ejemplo), tipado de datos de valores de atributos, rendimiento (obtención de atributos del producto, inserción o actualización de atributos del producto, etc.)

Si alguien más ha intentado una estrategia diferente con más éxito, me gustaría saberlo.

Gracias.

Answer 1

¿Su línea de productos cambia alguna vez?
Si lo hace, crear una tabla por producto te costará caro, y la idea de pares clave / valor te servirá. Ese es el tipo de dirección hacia abajo del cual estoy dibujado naturalmente.

Crearía tablas como esta:

Attribute(attribute_id, description, is_listed)    
-- contains values like "colour", "width", "power source", etc. 
-- "is_listed" tells us if we can get a list of valid values: 

AttributeValue(attribute_id, value)
-- lists of valid values for different attributes.  

Product (product_id, description)

ProductAttribute (product_id, attribute_id)  
-- tells us which attributes apply to which products

Order (order_id, etc)

OrderLine (order_id, order_line_id, product_id)

OrderLineProductAttributeValue (order_line_id, attribute_id, value)
-- tells us things like: order line 999 has "colour" of "blue"

El SQL para juntar esto no es trivial, pero tampoco es demasiado complejo ... y la mayor parte se escribirá una vez y se mantendrá (ya sea en procedimientos almacenados o en la capa de acceso a datos).

Hacemos cosas similares con varios tipos de entidades.

Answer 2

Chris y AJ: gracias por tus respuestas. La línea de productos puede cambiar, pero yo no lo llamaría "volátil".

La razón por la que no me gusta la tercera opción es que se aplica a los metadatos de los valores de los atributos del producto. Básicamente convierte las columnas en filas, perdiendo la mayoría de las ventajas de la columna de la base de datos en el proceso (tipo de datos, valor predeterminado, restricciones, relaciones de claves externas, etc.)

De hecho, he estado involucrado en un proyecto anterior donde la definición del producto se hizo de esta manera. Fundamentalmente creamos un sistema completo de definición de atributo producto / producto (tipos de datos, ocurrencias mín. / Máx., Valores predeterminados, indicadores "requeridos", escenarios de uso, etc.) El sistema funcionó, en última instancia, pero conllevaba un costo significativo en gastos generales y rendimiento ( por ejemplo, vistas materializadas para visualizar productos, componentes "inteligentes" personalizados para representar y validar la IU de entrada de datos para la definición del producto, otro componente "inteligente" para representar los atributos personalizables de la instancia del producto en la línea de pedido, blahblahblah).

De nuevo, gracias por tus respuestas!

Answer 3

No hay una bala mágica que hayas pasado por alto.

Tienes lo que algunas veces se llaman "subclases disjuntas". Está la superclase (Producto) con dos subclases (ProductoX) y (ProductoY). Este es un problema que, para las bases de datos relacionales, es realmente difícil. [Otro problema difícil es la Lista de materiales. Otro problema difícil es Gráficos de nodos y arcos.]

Realmente desea el polimorfismo, donde OrderLine está vinculado a una subclase de Producto, pero no sabe (ni le importa) qué subclase específica.

No tienes muchas opciones para modelar. Has identificado bastante las malas características de cada uno. Este es prácticamente todo el universo de elecciones.

1. Empuja todo hacia la superclase. Ese es el enfoque uni-table donde tiene Producto con un discriminador (tipo = "X" y tipo = "Y") y un millón de columnas. Las columnas de Producto son la unión de columnas en ProductX y ProductY. Habrá nulos por todas partes debido a las columnas no utilizadas.

2. Empuja todo hacia abajo en las subclases. En este caso, necesitará una vista que sea la unión de ProductX y ProductY. Esa vista es lo que se unió para crear un orden completo. Esta es como la primera solución, excepto que está construida dinámicamente y no se optimiza bien.

3. Únase a la instancia de Superclase a la instancia de subclase. En este caso, la tabla Producto es la intersección de las columnas ProductX y ProductY. Cada producto tiene una referencia a una clave en ProductX o ProductY.

Realmente no hay una nueva dirección audaz. En la vista mundial de la base de datos relacional, esas son las opciones.

Sin embargo, si elige cambiar la forma en que crea el software de la aplicación, puede salir de esta trampa. Si la aplicación está orientada a objetos, puede hacer todo con objetos polimórficos de primera clase. Tienes que mapear desde el procesamiento relacional tipo de torpe; esto ocurre dos veces: una vez cuando recuperas cosas de la base de datos para crear objetos y una vez cuando persistes objetos de vuelta a la base de datos.

La ventaja es que puede describir su procesamiento de manera concisa y correcta. Como objetos, con relaciones de subclase.

La desventaja es que su SQL depende de las incorporaciones, actualizaciones e inserciones masivas simplistas.

Esto se convierte en una ventaja cuando el SQL se aisla en una capa ORM y se gestiona como un tipo de detalle de implementación trivial. Los programadores de Java usan iBatis (o Hibernate o TopLink o Cocoon), los programadores de Python usan SQLAlchemy o SQLObject. El ORM hace que la base de datos recupere y guarde; su aplicación manipula directamente Órdenes, Líneas y Productos.

Answer 4

La primera solución que describa es la mejor si desea mantener la integridad de los datos, y si tiene relativamente pocos tipos de productos y rara vez agrega nuevos tipos de productos. Este es el diseño que elegiría en su situación. Los informes son complejos solo si sus informes necesitan los atributos específicos del producto. Si sus informes solo necesitan los atributos en la tabla de Productos comunes, está bien.

La segunda solución que describes se llama "Asociaciones polimórficas" y no sirve. Su "clave externa" no es una clave externa real, por lo que no puede usar una restricción de DRI para garantizar la integridad de los datos. El polimorfismo OO no tiene un análogo en el modelo relacional.

La tercera solución que describes, que implica almacenar un nombre de atributo como una cadena, es un diseño llamado "Entidad-Valor-Atributo" y puedes ver que esta es una solución costosa y dolorosa. No hay forma de garantizar la integridad de los datos, no hay forma de hacer que un atributo NO sea NULO, no hay forma de asegurarse de que un determinado producto tenga un determinado conjunto de atributos. No hay manera de restringir un atributo a una tabla de búsqueda. Muchos tipos de consultas agregadas se vuelven imposibles de hacer en SQL, por lo que debe escribir muchos códigos de aplicación para hacer informes. Utilice el diseño de EAV solo si debe, por ejemplo, si tiene un número ilimitado de tipos de productos, la lista de atributos puede ser diferente en cada fila, y su esquema debe acomodar nuevos tipos de productos frecuentemente, sin cambios de código o esquema.

Otra solución es "herencia de tabla única". Utiliza una tabla extremadamente amplia con una columna para cada atributo de cada producto. Deje NULLs en columnas que son irrelevantes para el producto en una fila determinada. Esto significa que no puede declarar un atributo como NOT NULL (a menos que esté en el grupo común a todos los productos). Además, la mayoría de los productos RDBMS tienen un límite en el número de columnas en una sola tabla, o el ancho total en bytes de una fila. Por lo tanto, tiene una cantidad limitada de tipos de productos que puede representar de esta manera.

Existen soluciones híbridas, por ejemplo, puede almacenar atributos comunes normalmente, en columnas, pero atributos específicos del producto en una tabla Entity-Attribute-Value. O bien, puede almacenar atributos específicos del producto de alguna otra manera estructurada, como XML o YAML, en una columna BLOB de la tabla Productos. Pero estas soluciones híbridas sufren porque ahora algunos atributos se deben buscar de una manera diferente

La solución definitiva para situaciones como esta es utilizar un modelo de datos semánticos, utilizando RDF en lugar de una base de datos relacional. Esto comparte algunas características con EAV pero es mucho más ambicioso. Todos los metadatos se almacenan de la misma manera que los datos, por lo que cada objeto es autodescriptivo y puede consultar la lista de atributos para un producto determinado tal como lo haría con los datos. Existen productos especiales, como Jena o Sesame , que implementan este modelo de datos y un lenguaje de consulta especial que es diferente de SQL.

Answer 5

Esto podría ayudarte a comenzar. Necesitará un refinamiento

Table Product ( id PK, name, price, units_per_package)
Table Product_Attribs (id FK ref Product, AttribName, AttribValue)

Lo que le permitiría adjuntar una lista de atributos a los productos. - Esta es esencialmente tu opción 3

Si conoce un número máximo de atributos, puede ir

Table Product (id PK, name, price, units_per_package, attrName_1, attrValue_1 ...)

Lo cual, por supuesto, desnormalizaría la base de datos, pero haría las consultas más fáciles.

Prefiero la primera opción porque

1. Admite una cantidad arbitraria de atributos.
2. Los nombres de atributos se pueden almacenar en otra tabla y se puede aplicar la integridad referencial para que esos malditos canadienses no pongan un "color" y rompan los informes.