Projektowanie schematu SQL dla kombinacji relacji wielu do wielu (odmiany produktów)

Mam nadzieję, że tytuł jest nieco pomocny. Używam MySQL jako bazy danych

Buduję bazę produktów i nie jestem pewien, jak poradzić sobie z przechowywaniem cen/SKU odmian produktu. Produkt może mieć nieograniczone wariacje, a każda kombinacja wariacji ma własną cenę/SKU / itp..

Tak właśnie ustawiam w tej chwili stół z produktami/wariacjami: 

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`)

Stworzyłem SQLFiddle z tymi przykładowymi danymi: http://sqlfiddle.com/#! 2 / 2264d/1

Użytkownik może wprowadzić dowolną nazwę wariacji (product_variants.variant) i przypisać do niej dowolną wartość (product_variants.value). nie powinno być ograniczenia ilości zmian / wartości, które użytkownik może wprowadzić.

Tutaj pojawia się mój problem: przechowywanie cen/SKU dla każdej odmiany bez dodawania nowej tabeli/kolumny za każdym razem, gdy ktoś dodaje produkt z wariantem, który wcześniej nie istniał.

Każdy wariant może mieć taką samą cenę, ale SKU jest unikalny dla każdego produktu. Na przykład Produkt 1 mA 6 różnych kombinacji (3 kolory * 2 Materiały), a produkt 2 ma tylko 3 różne kombinacje (3 rozmiary * 1).

Myślałem o zapisaniu kombinacji jako tekstu, czyli: 

+------------+-----------------+-------+------+
| 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 |
+------------+-----------------+-------+------+
Ale musi być lepszy, znormalizowany sposób przedstawiania tych danych. Hipotetyczna sytuacja: chcę być w stanie wyszukać niebieski produkt, który jest mniejszy niż $10. Przy powyższej strukturze bazy danych nie jest możliwe wykonanie bez parsowania tekstu i to jest coś, czego chcę uniknąć.

Każda pomoc / sugestie są mile widziane=)

Author: Mehdi Karamosly, 2013-10-02
Source

6 answers

Zastosowanie normalizacji do problemu rozwiązanie jest jak podano. Run and see it on 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);
 25
Author: sahalMoidu,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-10-06 05:18:18

Część twoich problemów wynika z pomyłki pomiędzy produktem a SKU.

Kiedy sprzedajesz "xyz sweter, Rozmiar M, Niebieski model", ten ostatni odpowiada SKU. Jest sprzedawany jako xyz pullover (produkt), który ma zestaw atrybutów (rozmiar i kolory), każdy z własnym zestawem wartości potencjału. I nie wszystkie możliwe kombinacje tych ostatnich mogą przynieść ważne rezultaty: nie znajdziesz absurdalnie cienkich i długich dżinsów. Sku, produkty, atrybuty, wartości atrybutów.

I kiedy użytkownik chce niebieski sweter za 10 dolarów, faktycznie szuka SKU w kategorii produktów.

Mam nadzieję, że powyższe rozwiąże Twoje zamieszanie i skąd wynikają Twoje problemy i pytania.

Jeśli chodzi o schemat, to chcesz coś takiego:

Produkty

  • #product_id
  • nazwa
  • Opis

Opcjonalnie Dodaj również:

  • cena
  • in_stock

To jest marketing / align = "left" / Nic więcej. Jeśli cokolwiek poza marketingiem używa produktu w Twojej aplikacji, skończysz w świecie bólu w dół drogi.

Cena, jeśli jest obecna, jest ceną główną używaną do wypełnienia pola, gdy jest null w SKU. Dzięki temu wpis cenowy jest bardziej przyjazny dla użytkownika.

In_stock jest flagą samoprzylepną, najlepiej utrzymywaną przez wyzwalacz. Powinno być prawdą, jeśli jakikolwiek SKU związany z tym produktem jest w stock.

Product_attributes

  • product_id
  • #attribute_id
  • nazwa

Product_attribute_values

  • attribute_id
  • #value_id
  • wartość

To po prostu przechowuje rzeczy takie jak KOLOR, ROZMIAR itp., wraz z ich wartościami jak niebieski, Czerwony, S, M, L.

Zwróć uwagę na pole product_id: utwórz nowy zestaw atrybutów i wartości dla produktu. Rozmiary zmieniają się w zależności od produktu. Czasami S, M, L itp.; innym razem będzie to 38, 40, 42, a co nie. Czasami wystarczy Rozmiar; innym razem potrzebujesz szerokości i długości. Niebieski może być prawidłowym kolorem dla tego produktu; Inny może oferować Granatowy, Królewski Niebieski, Morski i inne. Nie zakładaj, że istnieje jakikolwiek związek między atrybutami jednego produktu a atrybutami innego; podobieństwa, gdy istnieją, są całkowicie kosmetyczne i przypadek.

SKU

  • product_id
  • #sku_id
  • cena

Opcjonalnie dodaj:

  • nazwa
  • Kod kreskowy
  • stock

Odpowiada to dostawom, które zostaną wysłane.

To właściwie najważniejszy stół pod spodem. to , a nie product_id, jest prawie na pewno tym, o czym powinno się wspominać w zamówieniach klientów. Jest to również to, do czego należy się odwoływać dla magazynowanie i tak dalej. (Jedyny wyjątek, jaki kiedykolwiek widziałem do dwóch ostatnich punktów, to kiedy sprzedajesz coś naprawdę generycznego. Ale nawet wtedy, lepszym sposobem radzenia sobie z tym z mojego doświadczenia jest wrzucenie relacji n-m między wymiennymi SKU.)

Pole nazwa, jeśli je dodasz, jest przede wszystkim dla wygody. Jeśli pozostawisz null, użyj kodu po stronie aplikacji, aby odpowiadał nazwie generycznej produktu, rozszerzonej w razie potrzeby o odpowiednie nazwy atrybutów i wartości. Napełnianie pozwala na przeformułowanie tej ostatniej nazwy rodzajowej ("Levis' 501, W: 32, L: 32, Color: Dark Blue") na coś bardziej naturalnego ("Levis' 501, 32x32, Dark Blue").

W przypadku, gdy ma to znaczenie, zapasy są lepiej utrzymywane przy użyciu wyzwalacza w dłuższej perspektywie, z podwójnym schematem księgowym w tle. Pozwala to rozróżnić w magazynie i dostępne do wysyłki Dzisiaj (co jest liczbą, która rzeczywiście chcesz tutaj) vs w magazynie, ale już sprzedane, wśród tłumów realnego świata scenariusze, które napotkasz. Oh, I... czasami jest to Liczba, a nie liczba całkowita, jeśli kiedykolwiek potrzebujesz sprzedać coś mierzonego w kilogramach lub litrach. Jeśli tak, pamiętaj, aby dodać dodatkową flagę is_int, aby uniknąć klientów wysyłających Ci zamówienia .1 Laptop.

Product_variants

  • product_id
  • #sku_id
  • #attribute_id
  • value_id

To łączy identyfikator deliverable z odpowiednimi atrybutami i wartości, w celu generowania domyślnych nazw.

Klucz główny jest włączony (sku_id, attribute_id).

Pole product_id może być nieprawidłowe. Jest, chyba że dodasz klucze obce:

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

(nie zapomnij o dodatkowych unikalnych indeksach na odpowiednich krotkach, jeśli zdecydujesz się dodać te klucze obce.)

Trzy dodatkowe uwagi na zakończenie.

Po Pierwsze, chciałbym jeszcze raz podkreślić, że jeśli chodzi o przepływ, nie wszystkie kombinacje atrybutów i wartości dają prawidłowy wynik. Szerokość może wynosić 28-42, a długość może wynosić 28-42, ale prawdopodobnie nie zobaczysz poważnie chudych dżinsów 28x42. Najlepiej nie wypełniać automatycznie każdej możliwej odmiany każdego produktu domyślnie: Dodaj interfejs użytkownika, aby włączyć/wyłączyć je w razie potrzeby, ustaw go domyślnie zaznaczonym, obok pól Nazwa, Kod kreskowy i cena. (Nazwa i cena zwykle pozostaną puste; ale pewnego dnia będziesz musiał zorganizować sprzedaż tylko na niebieskich pulowerach, ponieważ kolor zostanie przerwany, podczas gdy nadal będziesz sprzedawać inne opcje.)

Po drugie, należy pamiętać, jeśli kiedykolwiek trzeba dodatkowo zarządzać opcjami produktu, że wiele faktycznie są atrybutami produktu w ukryciu, i że te, które nie są dają nowe SKU, które muszą być również brane pod uwagę, jeśli chodzi o magazynowanie. Większa opcja HD dla laptopa, na przykład, jest naprawdę wariant tego samego produktu (normalny vs duży rozmiar HD), który jest maskarada jako opcja ze względu na (bardzo ważne) względy UI. W przeciwieństwie do tego, owijanie laptopa jako prezentu świątecznego jest prawdziwą opcją, która ma odniesienia do całkowicie oddzielnego SKU w kategoriach księgowych (np.8m pakowania prezentów) - i, jeśli kiedykolwiek trzeba wymyślić średnie koszty krańcowe, ułamek czasu personelu.

Na koniec musisz przyjść up z metodą zamawiania dla atrybutów, ich wartości i kolejnych wariantów. W tym celu najłatwiej jest wrzucić Dodatkowe pole pozycji w tabelach atrybutów i wartości.

 12
Author: Denis de Bernardy,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-10-10 17:33:41

Użyłbym 4 tabel: 

generic_product: product_id, name, description

Np. 1, 'dywan', 'dywan do kawy' / 2, 'kubek','kubek do kawy' 

generic_product_property: product_id, property_id, property_name

Np. 1, 10, 'kolor' / 1, 11,'Materiał' 

sellable_product: sku, product_id, price

Np. "A121", 1, 50.00 / "A122", 1, 45.00

sellable_product_property: sku, property_id, property_value

Np. "A121", 10, "Czerwony" / "A121", 11, "wełna" / "A122", 10, "zielony" / "A122", 11, "wełna"

To pozwoli użytkownikowi zdefiniować dowolną właściwość dla sprzedawanych produktów, które chce.

Twoja aplikacja będzie musiała zapewnić swoją logikę biznesową że sellable_products są opisane całkowicie (sprawdź, czy dla każdej odpowiedniej właściwości produktu generycznego zdefiniowana jest właściwość produktu sprzedawanego).

 5
Author: xwoker,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-10-05 07:52:25

To jest podobne do innego pytania, które widziałem jakiś czas temu na tak

Projektowanie bazy danych: które podejście jest lepsze?

Jeśli spojrzysz tam, zobaczysz, że w zasadzie zadajesz to samo pytanie wąska (oparta na atrybutach) i szeroka tabela. Użyłem obu w zależności od scenariusza, ale byłbym bardzo ostrożny w sposobie, w jaki masz to zaimplementowane w tej chwili. I fakt, że naprawdę nie ma dobrego sposobu na dopasowanie tych wariantów do SKU (przynajmniej nie to Może zmusi cię do zmiany stolików.

Jeśli masz tak wiele różnych wariantów, możesz również zajrzeć do bazy danych z wartością klucza lub innego rozwiązania NoSQL.

 1
Author: Brad,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 12:00:25

Ogólnie rzecz biorąc, szukasz czegoś, co nazywa się grouper lub junk dimension. Zasadniczo jest to tylko wiersz dla każdej kombinacji.@ sahalMoidu wygląda na to, że powinien dać ci to, o co prosisz.

Ale zanim zaczniesz się zbytnio zastanawiać nad normalizacją, musisz wiedzieć, czy db jest tam do przechowywania danych (transakcyjnych, itp.) lub do pobierania danych (wymiarowych, raportowania, itp.). Nawet jeśli jest to transakcyjna baza danych, musisz zadać sobie pytanie, co próbujesz zrobić osiągnąć przez normalizację.

 1
Author: Andrew,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-10-09 15:39:02

Sku jest twoim głównym kluczem. Za pomocą sku można skonfigurować relacje kluczy obcych do tabeli wariantów. Zapomnij o productid całkowicie.

Utwórz tabelę x (SKU, CENA, OPIS) klucz podstawowy sku

 0
Author: danny117,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-10-11 13:46:57