Проектиране на 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);

Част от вашите проблеми произтичат от объркване между продукт и SKU.

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

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

Надявам се, че горното изяснява объркването ви и откъде произтичат вашият проблем и въпрос.

По отношение на схемата, вие искате нещо подобно:

продукти

• #идентификация на продукта
• име
• описание

По желание добавете също:

• цена
• в наличност

Това е таблица, свързана с маркетинг. Нищо друго. Ако нещо извън маркетинга използва продукт във вашето приложение, ще попаднете в свят на болка надолу по пътя.

Цената, ако присъства, е главна цена, използвана за попълване на полето, когато е нула в SKU. Това прави въвеждането на цена по-лесно за потребителя.

in_stock е, надяваме се, разбиращ се флаг, в идеалния случай поддържан от тригер. Трябва да е вярно, ако някоя SKU, свързана с този продукт, е на склад.

product_attributes

• идентификация на продукта
• #attribute_id
• име

стойности на_атрибут_на_продукт

• атрибут_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.)

Полето за име, ако го добавите, е предимно за удобство. Ако остане нула, използвайте код от страна на приложението, за да съответства на името на генеричния продукт, разширено, ако е необходимо, със съответните имена и стойности на атрибути. Попълването му позволява да се перифразира последното родово име („Levis' 501, W: 32, L: 32, Цвят: тъмно синьо“) с нещо по-естествено („Levis' 501, 32x32, тъмно синьо“).

В случай, че има значение, складът се поддържа по-добре с помощта на тригер в дългосрочен план, със схема за двойно счетоводство на заден план. Това позволява да се разграничи между наличност и наличност за доставка днес (което е цифрата, която всъщност искате тук) срещу наличност, но вече продадена, сред множеството сценарии от реалния свят, които ще срещнете. О, и... понякога е число, а не цяло число, ако някога трябва да продадете нещо, измерено в килограми или литри. Ако е така, не забравяйте да добавите допълнителен флаг is_int, за да избегнете клиентите да ви изпращат поръчки за .1 лаптопи.

продуктови_варианти

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

Това свързва идентификатора на доставката със съответните атрибути и стойности с цел генериране на имена по подразбиране.

Първичният ключ е включен (sku_id, attribute_id).

Може да намерите отклонение в полето product_id. Така е, освен ако не добавите външни ключове, препращащи към:

• Складови единици (product_id, sku_id)
• product_attributes (product_id, attribute_id)
• product_attribute_values ​​(attribute_id, value_id)

(Не забравяйте допълнителните уникални индекси на съответните кортежи, ако решите да добавите тези външни ключове.)

Три допълнителни забележки в заключение.

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

Второ, имайте предвид, ако някога ви се наложи да управлявате допълнително продуктови опции, че много от тях всъщност са прикрити продуктови атрибути и че тези, които не са, дават нови SKU, които също трябва да бъдат взети под внимание, когато става въпрос за съхраняване на склад. По-голяма HD опция за лаптоп, например, всъщност е вариант на същия продукт (нормален срещу голям HD размер), който се маскира като опция поради (много валидни) съображения за потребителския интерфейс. За разлика от това, опаковането на лаптопа като коледен подарък е истинска опция, която има препратки към напълно отделна SKU от счетоводна гледна точка (напр. 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 

напр. "A121", 10, "червено" / "A121", 11, "вълна" / "A122", 10, "зелено" / "A122", 11, "вълна"

Това ще позволи на вашия потребител да дефинира всяко свойство за вашите продаваеми продукти, което желае.

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

Това е подобно на друг въпрос, който видях преди време в SO

Проектиране на база данни: Кой е по-добрият подход?

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

Ако имате толкова много различни варианти, може също да искате да разгледате база данни с ключ-стойност или друго NoSQL решение.

Най-общо казано, вие търсите това, което се нарича grouper или junk измерение. По принцип това е само ред за всяка комбинация. Схемата на @sahalMoidu изглежда така, сякаш трябва да ви даде това, което искате.

Но преди да се захванете твърде много с нормализацията, трябва да знаете дали db е там за съхраняване на данни (транзакционни и т.н.) или за извличане на данни (размерни, отчитане и т.н.). Дори и да е транзакционна база данни, трябва да се запитате какво се опитвате да постигнете чрез нормализиране.

Sku е вашият основен ключ. Можете да настроите връзки на външен ключ към таблицата с варианти с sku. Забравете напълно за productid.

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