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

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

INSERT INTO product_variants
    (product_id, productvariantname, sku, price)
VALUES
    (1, 'red-wool', 'a121', 50),
    (1, 'red-polyester', 'a122', 50);

-- ========================================

CREATE TABLE product_details (
    product_detail_id   int AUTO_INCREMENT PRIMARY KEY,
    product_variants_id int,
    value_id            int
);

INSERT INTO product_details
    (product_variants_id, value_id)
VALUES
    (1, 1),
    (1, 4),
    (2, 1),
    (2, 5);

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

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

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

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

Что касается схемы, вам нужно что-то вроде этого:

продукты

• #идантификационный номер продукта
• название
• описание

При желании также добавьте:

• цена
• в наличии

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

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

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

product_attributes

• идантификационный номер продукта
• #attribute_id
• название

product_attribute_values

• attribute_id
• #value_id
• ценность

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

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

SKU

• идантификационный номер продукта
• #sku_id
• цена

При желании можно добавить:

• название
• штрих-код
• склад

Это соответствует поставленным результатам.

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

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

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

product_variants

• идантификационный номер продукта
• #sku_id
• #attribute_id
• value_id

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

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

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

• Артикулы (product_id, sku_id)
• атрибуты продукта (product_id, attribute_id)
• product_attribute_values ​​(attribute_id, value_id)

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

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

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

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

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

Я бы использовал 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 

e.g. 'A121', 1, 50.00 / 'A122', 1, 45.00

sellable_product_property: sku, property_id, property_value 

например «А121», 10, «красный» / «А121», 11, «шерсть» / «А122», 10, «зеленый» / «А122», 11, «шерсть»

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

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

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

Создание базы данных: какой подход лучше?

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

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

Sku - ваш первичный ключ. Вы можете настроить отношения внешнего ключа с таблицей вариантов с помощью sku. Полностью забудьте о продуктах.

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