sql - una - relacion uno a varios access

Diseñar un esquema SQL para una combinación de relación de muchos a muchos(variaciones de productos)

Question

Espero que el título sea algo útil. Estoy usando MySQL como mi base de datos

Estoy creando una base de datos de productos y no estoy seguro de cómo manejar los precios de almacenamiento / SKU de las variaciones de un producto. Un producto puede tener variaciones ilimitadas, y cada combinación de variación tiene su propio precio / SKU / etc.

Así es como tengo mi tabla de productos / variaciones configurada en este momento:

PRODUCTS
+--------------------------+
| id | name | description  |
+----+------+--------------+
| 1  | rug  | a cool rug   |
| 2  | cup  | a coffee cup |
+----+------+--------------+

PRODUCT_VARIANTS
+----+------------+----------+-----------+
| id | product_id | variant  | value     |
+----+------------+----------+-----------+
| 1  | 1          | color    | red       |
| 2  | 1          | color    | blue      |
| 3  | 1          | color    | green     |
| 4  | 1          | material | wool      |
| 5  | 1          | material | polyester |
| 6  | 2          | size     | small     |
| 7  | 2          | size     | medium    |
| 8  | 2          | size     | large     |
+----+------------+----------+-----------+

(`products.id` is a foreign key of `product_variants.product_id`)

He creado un SQLFiddle con estos datos de muestra: http://sqlfiddle.com/#!2/2264d/1

El usuario puede ingresar cualquier nombre de variación ( product_variants.variant ) y puede asignarle cualquier valor ( product_variants.value ). No debe haber un límite en la cantidad de variaciones / valores que un usuario puede ingresar.

Aquí es donde surge mi problema: almacenar precios / SKU para cada variación sin agregar una nueva tabla / columna cada vez que alguien agrega un producto con una variante que no existía antes.

Cada variante puede tener el mismo precio, pero el SKU es único para cada producto. Por ejemplo, el Producto 1 tiene 6 combinaciones diferentes (3 colores * 2 materiales) y el Producto 2 solo tiene 3 combinaciones diferentes (3 tamaños * 1).

He pensado en almacenar las combinaciones como un texto, es decir:

+------------+-----------------+-------+------+
| product_id | combination     | price | SKU  |
+------------+-----------------+-------+------+
| 1          | red-wool        | 50.00 | A121 |
| 1          | red-polyester   | 50.00 | A122 |
| 1          | blue-wool       | 50.00 | A123 |
| 1          | blue-polyester  | 50.00 | A124 |
| 1          | green-wool      | 50.00 | A125 |
| 1          | green-polyester | 50.00 | A125 |
| 2          | small           | 4.00  | CD12 |
| 2          | medium          | 4.00  | CD13 |
| 2          | large           | 3.50  | CD14 |
+------------+-----------------+-------+------+

Pero debe haber una manera mejor, normalizada, de representar estos datos. Situación hipotética: quiero poder buscar un producto azul que sea menos de $ 10. Con la estructura de base de datos anterior no es posible hacerlo sin analizar el texto y eso es algo que quiero evitar.

Cualquier ayuda / sugerencias son apreciadas =)

Answer 1

Aplicando la normalización a su problema, la solución es la dada. Corre y velo en Fiddle

Fiddle

CREATE TABLE products 
    (
     product_id int auto_increment primary key, 
     name varchar(20), 
     description varchar(30)

    );

INSERT INTO products
(name, description)
VALUES
(''Rug'', ''A cool rug''  ),
(''Cup'', ''A coffee cup'');

create table variants (variant_id int auto_increment primary key,
                       variant varchar(50)
                       );
insert into variants (variant)
values (''color''),(''material''),(''size'') ;   
create table variant_value(value_id int auto_increment primary key, 
                           variant_id int ,
                           value varchar(50)
                           );

insert into variant_value (variant_id,value)
values (1 ,''red''),(1 ,''blue''),(1 ,''green''),
        (2 ,''wool''),(2 ,''polyester''),
        (3 ,''small''),(3 ,''medium''),(3 ,''large'');



create table product_Variants( product_Variants_id int  auto_increment primary key,
                            product_id int,
                            productVariantName varchar(50),
                            sku varchar(50),
                            price float
                            );




create table product_details(product_detail_id int auto_increment primary key,
                             product_Variants_id int,

                             value_id int
                             );

insert into product_Variants(product_id,productVariantName,sku,price)
values (1,''red-wool'' ,''a121'',50);

insert into product_details(product_Variants_id , value_id)
values( 1,1),(1,4);

insert into product_Variants(product_id,productVariantName,sku,price)
values (1,''red-polyester'' ,''a122'',50);

insert into product_details(product_Variants_id , value_id)
values( 2,1),(2,5);

Answer 2

En términos generales, estás buscando lo que se llama un mero o una dimensión de basura. Básicamente, es solo una fila para cada combinación. El esquema de @ sahalMoidu parece que debería darte lo que estás pidiendo.

Pero antes de colgar demasiado en la normalización, necesita saber si la base de datos está allí para almacenar datos (transaccionales, etc.) o para obtener datos (dimensiones, informes, etc.). Incluso si se trata de una base de datos transaccional, debe preguntarse qué intenta lograr con la normalización.

Answer 3

Esto es similar a otra pregunta que vi hace un tiempo atrás en SO

Diseñando una base de datos: ¿Cuál es el mejor enfoque?

Si miras allí, verás que básicamente estás haciendo la misma pregunta de tabla estrecha (basada en atributos) frente a amplia. He usado ambos dependiendo del escenario, pero sería muy cuidadoso con la forma en que lo implementó ahora. Y el hecho de que realmente no haya una buena manera de hacer coincidir esas variantes con los SKU (al menos no puedo pensar en ello) puede obligarte a cambiar tus tablas.

Si tiene tantas variantes diferentes, es posible que también desee consultar una base de datos de valores clave, o alguna otra solución NoSQL.

Answer 4

Parte de sus problemas proviene de una confusión entre el producto y SKU.

Cuando vende "XYZ pullover, talla M, modelo azul", este último corresponde a un SKU. Se comercializa como un jersey XYZ (el producto), que tiene un conjunto de atributos (tamaño y colores), cada uno con su propio conjunto de valores potenciales. Y no todas las combinaciones posibles de este último pueden proporcionar resultados válidos: no encontrará pantalones vaqueros delgados y largos absurdamente. SKUs, productos, atributos, valores de atributos.

Y cuando un usuario quiere un jersey azul de $ 10, está buscando un SKU dentro de una categoría de producto.

Espero que lo anterior aclare su confusión y de dónde surgen su problema y pregunta.

En términos de esquema, quieres algo como esto:

productos

• #identificación de producto
• nombre
• descripción

Opcionalmente, también añadir:

• precio
• en stock

Esta es una tabla relacionada con la comercialización . Nada más. Si algo fuera del marketing utiliza un producto en su aplicación, terminará en un mundo de dolor en el futuro.

El precio, si está presente, es un precio maestro utilizado para rellenar el campo cuando es nulo en los SKU. Esto hace que la entrada de precios sea más fácil de usar.

in_stock es una bandera con una explicación autoexplicativa, mantenida idealmente por un disparador. Debe ser cierto si hay algún SKU relacionado con ese producto en stock.

atributos del producto

• identificación de producto
• #attribute_id
• nombre

product_attribute_values

• atributo_id
• #valor_id
• valor

Esto solo contiene cosas como Color, Tamaño, etc., junto con sus valores como azul, rojo, S, M, L.

Tenga en cuenta el campo product_id: cree un nuevo conjunto de atributos y valores por producto . Los tamaños cambian según el producto. A veces es S, M, L, etc .; Otras veces, será 38, 40, 42, y lo que no. A veces, el tamaño es suficiente; otras veces, necesitas ancho y largo. El azul puede ser un color válido para este producto; otro podría ofrecer a la Marina, azul real, verde azulado y lo que no. NO asuma que existe una relación entre los atributos de un producto y los de otro; Las similitudes, cuando existen, son totalmente cosméticas y casuales.

SKUs

• identificación de producto
• #sku_id
• precio

Opcionalmente, agregue:

• nombre
• código de barras
• valores

Esto corresponde a los entregables que se envían.

En realidad es la mesa más importante debajo. Esto , en lugar de product_id, es casi seguramente lo que se debe hacer referencia en los pedidos de los clientes. También es a lo que se debe hacer referencia para el mantenimiento de existencias, etc. (La única excepción que he visto en los últimos dos puntos es cuando vendes algo realmente genérico. Pero incluso así, la mejor manera de lidiar con esto en mi experiencia es sumar una relación nm entre SKU intercambiables).

El campo de nombre, si lo agrega, es principalmente por conveniencia. Si se deja nulo, use el código del lado de la aplicación para que se corresponda con el nombre del producto genérico, expandido si es necesario con los nombres y valores de los atributos relevantes. Rellenarlo permite reformular el último nombre genérico ("Levis ''501, W: 32, L: 32, Color: Dark Blue") con algo más natural ("Levis'' 501, 32x32, Dark Blue").

En caso de que sea importante, el stock se mantiene mejor utilizando un desencadenante a largo plazo, con un esquema de contabilidad de doble entrada en segundo plano. Esto permite distinguir entre el stock en existencia y el disponible en la actualidad (que es la cifra que realmente quiere aquí) frente al stock, pero ya vendido, entre las multitud de escenarios del mundo real que encontrará. Ah, y ... ocasionalmente es un número, en lugar de un número entero, si alguna vez necesitas vender algo medido en kilos o litros. Si es así, asegúrese de agregar una marca is_int adicional para evitar que los clientes le envíen pedidos de computadoras portátiles .1.

producto_varianzas

• identificación de producto
• #sku_id
• #attribute_id
• value_id

Esto vincula la identificación del entregable con los atributos y valores correspondientes, con el fin de generar nombres predeterminados.

La clave principal está activada (sku_id, attribute_id).

Es posible que encuentre el campo product_id un aberrance. Es, a menos que agregue referencias de claves externas:

• SKU (product_id, sku_id)
• atributos_productos (product_id, attribute_id)
• product_attribute_values ​​(attribute_id, value_id)

(No olvide los índices únicos adicionales en las tuplas correspondientes si decide agregar estas claves externas).

Tres observaciones adicionales en conclusión.

En primer lugar, me gustaría enfatizar una vez más que, en términos de flujo, no todas las combinaciones de atributos y valores producen un resultado válido. El ancho puede ser 28-42 y la longitud puede ser 28-42, pero probablemente no veas unos jeans 28x42 muy delgados. Es preferible NO rellenar automáticamente cada variación posible de cada producto de forma predeterminada: agregue la IU para habilitarlos / deshabilitarlos según sea necesario, haga que se verifique de manera predeterminada, junto con los campos de nombre, código de barras y precio. (Por lo general, el nombre y el precio se dejarán en blanco; pero un día, tendrá que organizar una venta solo con jerseys azules, debido a que el color se ha suspendido, mientras continúa vendiendo las otras opciones).

En segundo lugar, tenga en cuenta que, si alguna vez necesita administrar adicionalmente las opciones de productos, muchos de ellos son atributos del producto disfrazados, y que aquellos que no son productos nuevos SKU que también deben tenerse en cuenta cuando se trata de mantenimiento de existencias. Una opción de HD más grande para una computadora portátil, por ejemplo, es realmente una variante del mismo producto (tamaño de HD normal o grande) que se enmascara como una opción debido a consideraciones de UI (muy válidas). En contraste, envolver la computadora portátil como un regalo de Navidad es una opción genuina que tiene referencias de un SKU completamente separado en términos de contabilidad (por ejemplo, .8m de papel de regalo) - y, si alguna vez necesita llegar a un costo marginal promedio, una fracción de tiempo del personal.

Por último, deberá crear un método de pedido para sus atributos, sus valores y las variantes posteriores. Para esto, lo más fácil es tirar un campo de posición extra en las tablas de atributos y valores.

Answer 5

Sku es tu clave principal. Puede configurar relaciones de clave externa a la tabla de variantes con sku. Olvídate de productid por completo.

Crear tabla x (sku, precio, descripción) clave principal sku

Answer 6

Yo usaría 4 tablas:

generic_product: product_id, name, description 

por ejemplo, 1, ''alfombra'', ''una alfombra de café'' / 2, ''taza'', ''una taza de café''

generic_product_property: product_id, property_id, property_name 

por ejemplo, 1, 10, ''color'' / 1, 11, ''material''

sellable_product: sku, product_id, price 

por ejemplo, ''A121'', 1, 50.00 / ''A122'', 1, 45.00

sellable_product_property: sku, property_id, property_value 

por ejemplo, ''A121'', 10, ''rojo'' / ''A121'', 11, ''lana'' / ''A122'', 10, ''verde'' / ''A122'', 11, ''lana''

Esto le permitirá a su usuario definir cualquier propiedad para los productos vendibles que desea.

Su aplicación deberá garantizar con su lógica empresarial que los productos sellable se describan completamente (verifique que para cada propiedad genérica aplicable del producto se defina la propiedad vendible del producto).