Проектирование схемы SQL для комбинации отношений "многие-ко-многим" (варианты продуктов)

Ответ 1

Применяя нормализацию к вашей проблеме, решение задано. Запустите и посмотрите на 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);

Ответ 2

Часть ваших проблем связана с путаницей между продуктом и SKU.

Когда вы продаете "пуловер XYZ, размер M, синяя модель", последний соответствует SKU. Он продается как пуловер XYZ (продукт), который имеет набор атрибутов (размер и цвета), каждый со своим набором потенциальных значений. И не все возможные комбинации последних могут привести к достоверным результатам: вы не найдете абсурдно тонких и длинных джинсов. SKU, продукты, атрибуты, значения атрибутов.

И когда пользователь хочет получить пуловер $10, он действительно ищет SKU в категории продукта.

Надеюсь, что вышеизложенное очистит ваше замешательство и где возникла ваша проблема и вопрос.

В терминах схемы вы хотите что-то вроде этого:

Продукты

• #product_id
• имя
• Описание

Дополнительно можно добавить:

• цена
• in_stock

Это маркетинговая таблица. Ничего больше. Если что-либо вне маркетинга использует продукт в вашем приложении, вы окажетесь в мире боли по дороге.

Цена, если она есть, является основной ценой, используемой для заполнения поля, когда оно недействительно в единицах SKU. Это делает введение цены более удобным.

in_stock - надежный самообучающийся флаг, идеально поддерживаемый триггером. Это должно быть правдой, если какой-либо SKU, связанный с этим продуктом, находится на складе.

product_attributes

• product_id
• #attribute_id
• имя

product_attribute_values ​​

• attribute_id
• #value_id
• значение

Это просто хранит такие вещи, как цвет, размер и т.д., а также их значения, такие как синий, красный, S, M, L.

Обратите внимание на поле product_id: создать новый набор атрибутов и значений для продукта. Размеры изменяются в зависимости от продукта. Иногда это S, M, L и т.д.; в других случаях это будет 38, 40, 42, а что нет. Иногда размер достаточно; в других случаях вам нужна ширина и длина. Синий может быть допустимым цветом для этого продукта; другой может предложить ВМС, Королевский Синий, Тил, а что нет. НЕ предполагайте, что существует какая-либо связь между атрибутами одного продукта и атрибутами другого; сходства, когда они существуют, являются полностью косметическими и случайными.

SKUs

• product_id
• #sku_id
• цена

При необходимости добавьте:

• имя
• штрих-кода
• акции

Это соответствует отправляемым материалам.

На самом деле это самая важная таблица внизу. Это, а не product_id, почти наверняка, что нужно указывать в заказах клиентов. Это также то, на что следует ссылаться на запасы и т.д. (Единственное исключение, которое я когда-либо видел в двух последних моментах, - это когда вы продаете что-то действительно общее. Но даже тогда лучший способ справиться с этим в моем опыте - бросить в связь nm между взаимозаменяемыми SKU.)

Поле имени, если вы его добавите, предназначено в первую очередь для удобства. Если left null, используйте код приложения, чтобы он соответствовал имени универсального продукта, при необходимости расширив его соответствующими именами атрибутов и значениями. Заполнение его позволяет перефразировать последнее общее имя ( "Levis" 501, W: 32, L: 32, Color: Dark Blue ") с чем-то более естественным (" Levis "501, 32x32, Dark Blue).

В случае, если это имеет значение, запас лучше поддерживать с помощью триггера в конечном счете с двойной схемой бухгалтерского учета в фоновом режиме. Это позволяет провести различие между запасами и доступными для отгрузки сегодня (это то, что вы на самом деле хотите здесь) против запасов, но уже проданных, среди множества реальных сценариев, с которыми вы столкнетесь. О, и... иногда это числовое, а не целое число, если вам когда-либо нужно продавать что-либо, измеренное в килограммах или литрах. Если это так, не забудьте добавить дополнительный флаг is_int, чтобы не отправлять клиентам заказы на .1 ноутбуки.

product_variants

• product_id
• #sku_id
• #attribute_id
• value_id

Это связывает поставляемый идентификатор с соответствующими атрибутами и значениями для генерации имен по умолчанию.

Первичный ключ включен (sku_id, attribute_id).

Возможно, вы найдете поле product_id аберрансом. Это, если вы не добавили внешние ключи, ссылающиеся на:

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

(Не забудьте добавить дополнительные уникальные индексы в соответствующие кортежи, если вы решите добавить эти внешние ключи.)

Три дополнительных замечания в заключение.

Во-первых, я хотел бы еще раз подчеркнуть, что с точки зрения потока не все комбинации атрибутов и значений дают допустимый результат. Ширина может составлять 28-42, а длина может составлять 28-42, но вы, вероятно, не увидите сильно тощих 28x42 джинсов. Лучше всего НЕ автоматически заполнять все возможные варианты каждого продукта по умолчанию: добавьте интерфейс, чтобы включить/отключить их по мере необходимости, сделать его проверенным по умолчанию вместе с именем, штрих-кодом и полями цены. (Название и цена обычно остаются пустыми, но в один прекрасный день вам нужно будет организовать продажу только на синих пуловерках, исходя из того, что цвет прекращен, а вы продолжаете продавать другие варианты.)

Во-вторых, имейте в виду, если вам когда-либо понадобится дополнительно управлять параметрами продукта, многие на самом деле являются атрибутами продукта, которые скрыты, и что те, которые не дают новых SKU, которые также должны быть приняты во внимание, когда речь идет о запасах -keeping. Например, более крупный вариант HD для ноутбука - это вариант одного и того же продукта (обычный и большой размер HD), который маскируется как опция из-за (очень правильных) соображений пользовательского интерфейса. В отличие от этого, упаковка ноутбука в качестве рождественского подарка является подлинным вариантом, который имеет ссылки на совершенно отдельный SKU в бухгалтерском учете (например, на подарочной упаковке на сумму 8 м) - и если вам когда-нибудь понадобится средняя предельная стоимость, времени персонала.

Наконец, вам нужно придумать метод упорядочения для ваших атрибутов, их значений и последующих вариантов. Для этого проще всего вбросить поле дополнительной позиции в таблицы атрибутов и значений.

Ответ 3

Я бы использовал 4 таблицы:

generic_product: product_id, name, description 

например. 1, "ковер", "кофейный ковер" /2, "кружка", "кружка кофе"

generic_product_property: product_id, property_id, property_name 

например. 1, 10, "цвет" /1, 11, "материал"

sellable_product: sku, product_id, price 

например. 'A121', 1, 50,00/'A122', 1, 45,00

sellable_product_property: sku, property_id, property_value 

например. 'A121', 10, 'red'/'A121', 11, 'wool'/'A122', 10, 'green'/'A122', 11, 'wool'

Это позволит вашему пользователю определить какое-либо свойство для ваших продаваемых продуктов, которые он хочет.

Ваше приложение должно будет обеспечить своей бизнес-логикой, что sellable_products будут описаны полностью (убедитесь, что для каждого применимого свойства универсального продукта определено свойство продаваемого продукта).

Ответ 4

Это похоже на другой вопрос, который я видел некоторое время назад на SO

Проектирование базы данных: какой из них лучше?

Если вы посмотрите там, вы увидите, что в основном задаете один и тот же узкий (на основе атрибута) и широкоформатный вопрос. Я использовал оба варианта в зависимости от сценария, но я был бы очень осторожен, как вы его реализовали прямо сейчас. И тот факт, что на самом деле не очень хороший способ сопоставить эти варианты с SKU (по крайней мере, не так, как я могу думать), может заставить вас изменить ваши таблицы.

Если у вас так много разных вариантов, вы также можете захотеть заглянуть в базу данных с ключом или какое-то другое решение NoSQL.

Ответ 5

В общих чертах вы ищете то, что называется групповым или мусорным. В основном это просто строка для каждой комбинации. Схема @sahalMoidu выглядит так, как будто она должна дать вам то, о чем вы просите.

Но прежде чем слишком зависеть от нормализации, вам нужно знать, существует ли db для хранения данных (транзакционных и т.д.) или для получения данных (размерность, отчетность и т.д.). Даже если это транзакционная база данных, вы должны спросить себя, чего вы пытаетесь добиться путем нормализации.

Ответ 6

Ску - ваш первичный ключ. Вы можете настроить отношения внешнего ключа в таблице вариантов со Ску. Забудьте о productid целиком.

Создать таблицу x (sku, цена, описание) первичный ключ sku