Schemat bazy danych do wyceny produktów (pakiety, promocje, oparte na ilości, oferta ograniczona czasowo…)

Pracuję nad nowym punktem sprzedaży dla firmy, która oferuje produkty w różnych cenach w zależności od asortymentu.

Wszystkie produkty mają cenę podstawową.

Aby wyjaśnić mój problem, użyję następujących informacji:

Product         Category        Price
A               1               45
B               1               70
Q               2               20
R               2               27
S               2               15
X               3               17
Y               3               22
Z               3               16

Firma ma Pakiety, na przykład Pakiet „Combo”: dla produktu A lub B, jeśli wybierzesz 1 z Q lub R i 1 z X, Y lub Z, otrzymasz rabat w wysokości 20 USD.

Przypadek A: Czasami klienci dodają produkt bazowy przy składaniu zamówienia, na przykład: Nie chcą jednego z Produktu A i dodają do tego Produkt Q i Produkt P, aby utworzyć pakiet z obniżoną ceną. Następnie mogą dodać, że chcą 1 produktu B z 1 R i 1 Z.

Przypadek B: Czasami klienci dodają 1 A i 2 B, 2 Q, 1 S, 2 X i 1 Z. Zgodnie z zasadami określonymi w pakiecie „Combo” obowiązują tylko 2 kombinacje, ponieważ S nie jest przedmiotem kombinacji.

Inne promocje zależą od ilości, więc jeśli kupisz 2 B, otrzymasz 20% zniżki i / lub zależy od czasu, to jest ważne tylko po 17:00 lub przed 10% zniżki, jeśli przed 10 rano. Kolejna promocja może zależeć od tego, kiedy nastąpił ostatni zakup lub czy zakupiłeś więcej niż X USD w terminie Y.

Moje problemy:

1) Jak ustrukturyzować tabele, aby tworzyć różne pakiety lub promocje w sposób bardzo elastyczny, aby dodawać różne typy promocji o różnych wymaganiach?

2) Kiedy zamawiają, jak przypadek B (lub połączenie przypadku A i przypadku B), w jaki sposób ustrukturyzować moje zapytanie, aby móc przetestować, aby zobaczyć, które produkty są w porządku i odpowiednio zaktualizować ceny / opisy ? Ostatecznie najlepszy wynik dla tego zapytania zwróci, które pakiety i promocje spełniają wymagania, w kolejności, która daje największą korzyść klientowi (tj. Może to, co zamówili, spełnia wymagania dla promocji 1 i 3, ale promocja 3 jest tańsza. musi działać z wieloma promocjami).

Z góry dziękuję za pomoc!

AKTUALIZACJA # 1

Aby lepiej opisać bieżące problemy i zaktualizować pracę wykonaną do tej pory, aby je rozwiązać , włączam ERD Modelu Produktu ograniczonego do podmiotów i atrybutów, które wpływają na problem (tzn. Nie ma tu zapasów, więc brak zapasów podmioty są obecne).

Podaję również przykładowe dane z encji i atrybutów, które wpływają na to pytanie (aby uprościć czytanie danych, umieszczam nazwy / opisy zamiast kluczy obcych):

Oto link do schematu blokowego, podający przykład kombinacji, szybki i wizualny sposób na zrozumienie struktury tabeli.

PRODUCT
---------
ID  Name
================================
1   Hamburger
2   Cheeseburger
3   Bacon Hamburger
4   Bacon Cheeseburger
5   Orange Juice
6   Apple Juice
7   Coffee
8   Coke
9   French Fries
10  Onion Rings
11  Soup du Jour
12  Hamburger Combo
13  CheeseBurger Combo
14  Bacon Hamburger Combo
15  Bacon Cheeseburger Combo
16  Combo Side
17  Combo Beverage
18  Small Orange Juice
19  Large Orange Juice
20  Small Apple Juice
21  Large Apple Juice
22  Add Extra Patty
23  Add Avocado

PRODUCT COMPONENT
------------------
productFrom                 productTo       
===================================================
Hamburger Combo             Hamburger
Hamburger Combo             Combo Side
Hamburger Combo             Combo Beverage
CheeseBurger Combo          Cheeseburger
CheeseBurger Combo          Combo Side
CheeseBurger Combo          Combo Beverage
Bacon Hamburger Combo       Bacon Hamburger
Bacon Hamburger Combo       Combo Side
Bacon Hamburger Combo       Combo Beverage
Bacon Cheeseburger Combo    Bacon Cheeseburger
Bacon Cheeseburger Combo    Combo Side
Bacon Cheeseburger Combo    Combo Beverage

PRODUCT FEATURE
----------------
ID  Description
=======================
1   Combo Side Option
2   Combo Beverage
3   Juice
4   Orange Juice Size
5   Apple Juice Size
6   Extras

PRODUCT FEATURE APPLICABILITY
------------------------------
product                     productFeature  ProductFeatureApplicabilityType
============================================================================
Hamburger Combo             Combo Side      Required
Hamburger Combo             Juice Flavor    Required
Cheeseburger Combo          Combo Side      Required
Cheeseburger Combo          Juice Flavor    Required
Bacon Hamburger Combo       Combo Side      Required
Bacon Hamburger Combo       Juice Flavor    Required
Bacon Cheeseburger Combo    Combo Side      Required
Bacon Cheeseburger Combo    Juice Flavor    Required


PRODUCT FEATURE APPLICABILITY
------------------------------
productFeature  product             ProductFeatureApplicabilityType
============================================================================
Combo Side      Hamburger Combo             Required
Combo Beverage  Hamburger Combo             Required
Extras          Hamburger Combo             Optional
Combo Side      Cheeseburger Combo          Required
Combo Beverage  Cheeseburger Combo          Required
Extras          Cheeseburger Combo          Optional
Combo Side      Bacon Hamburger Combo       Required
Combo Beverage  Bacon Hamburger Combo       Required
Extras          Bacon Hamburger Combo       Optional
Combo Side      Bacon Cheeseburger Combo    Required
Combo Beverage  Bacon Cheeseburger Combo    Required
Extras          Bacon Cheeseburger Combo    Optional




OPTIONAL FEATURE
------------------
productFeatureFrom  Product             ProductFeatureTo        
=============================================================
Combo Side Option   French Fries
Combo Side Option   Onion Rings
Combo Side Option   Soup du Jour
Combo Beverage                          Juice
Combo Beverage      Coffee
Juice                                   Orange Juice Size
Juice                                   Apple Juice Size
Orange Juice Size   Orange Apple Juice
Orange Juice Size   Orange Apple Juice
Apple Juice Size    Small Apple Juice
Apple Juice Size    Large Apple Juice
Extras              Add Extra Patty
Extras              Add Avocado

Tak więc dzięki badaniom i wsparciu ze strony społeczności do czasu, gdy udało mi się dowiedzieć, udało mi się rozwiązać problem nr 1. W rzeczywistości zrobiłem to z większą elastycznością, niż myślałem, że mogę to zrobić przy pierwszym wdrożeniu systemu.

Pomimo postępów w Problemie 2, nie jest on rozwiązany w sposób zadowalający. Były pewne pomysły, jak to zrobić, Neil McGuilgan zadał świetne pytanie prowadzące do możliwego rozwiązania za pomocą Relational Division (dba.stackexchange.com/questions/45829/what-is-the-name-th-this-type- of-query-and-what-is-an-wydajne-przykład) i ta książka (www.amazon.com/books/dp/0471380237) bardzo pomogła. Jednak obecnie to rozwiązanie i, jak rozumiem, działa tylko z „jednym” rekordem (kombinacją) na raz. Jeśli klient podchodzi i mówi, że chce 2 cheeseburgery, 1 hamburger, 1 mały sok jabłkowy, 1 colę, 1 frytkę i 2 krążki cebuli, potrzebuję sposobu, aby wykryć, że w mieszance jest tylko jedna kombinacja, a drugą dodać produkty w cenie bazowej. Jeśli istnieje wiele kombinacji kombinacji, „

Jednym z pomysłów, które wpadłem na pomysł, aby rozwiązać problem drugi, jest dodanie i przypisanie flaggin PRODUCT COMPONENT głównego produktu dla kombinacji (np. Hamburgera). Następnie podczas uruchamiania procesu wyceny zapytaj, które produkty w zamówieniu są głównymi produktami w „pakiecie”, odnosząc zapytanie do rabatu podanego w tabeli CENA KOMPONENTU i zamawiając według tej wartości (malejąco), a następnie w tej kolejności paczek sprawdź aby sprawdzić, czy można utworzyć „pakiet” z pozostałymi produktami innymi niż główne za pomocą zapytania i zapętlić proces, aż nie będzie więcej produktów głównych lub nie będzie już produktów innych niż produkty główne.

To może się skomplikować ...

1) Jak ustrukturyzować tabele, aby tworzyć różne pakiety lub promocje w sposób bardzo elastyczny, aby dodawać różne typy promocji o różnych wymaganiach?

Możesz zacząć od packaged_withtabeli, aby określić, które produkty można grupować i pakować razem:

pakiet
-------
  id (PK)
  imię

grupa_pakietu
-------------
  pakiet_id (FK do package.id)
  imię

packaged_with
-------------
  id_grupy_pakietów (FK na id_grupy_pakietów)
  product_id (FK do product.id)
  can_be_packaged_with (FK do product.id)

package_groupodnosi się do package. packaged_withodnosi się do productsi package_groups, dzięki czemu wiersz w wierszu packaged_withpokazuje, z którymi produktami można zapakować produkt, a paczka może składać się z wielu grup.

Dane wyglądałyby następująco:

pakiet
-------
ID | imię
------------
1 | Kombinacja

grupa_pakietu
------------
ID | imię
---------
1 | Grupa QR
2 | Grupa XYZ

packaged_with
-------------
id_grupy_pakietów | ID_produktu | can_be_packaged_with
----------------------------------------------
1 | A | Q
1 | A | R
2 | A | X
2 | A | Y
2 | A | Z
1 | B | Q
1 | B | R
2 | B | X
2 | B | Y
2 | B | Z

Pomoże to w samych pakietach produktów. Mam kilka pomysłów na resztę twojego pytania, ale nie mam teraz czasu na dokończenie tej odpowiedzi ...

Promocje

Wymieniasz wiele rodzajów promocji. Można zajrzeć do jakiejś zasady silnikiem, ale postaram się zachować rzeczy prostsze, ale wciąż ... To będzie się bardziej skomplikowane ..

Zacznijmy od prostych promocji, w których cena jest obniżana o pewien procent:

percent_discount
----------------
  id (PK)
  imię
  kwota_procentowa

product_promotions
------------------
  id (PK)
  product_id (FK do product.id)
  Promotion_id (FK na percent_discount.id)
  Data rozpoczęcia
  Data zakonczenia

Tutaj mamy tabelę, która przechowuje jaki procent otrzymujesz za produkt. Kolejna tabela faktycznie łączy produkty ze stopą dyskontową, a także zawiera datę początkową i końcową, więc wiesz, czy zniżka obowiązuje w danym dniu.

Pomysły na inne promocje, które pojawią się później ...

Chociaż jest to 3-letni wątek, nadal odpowiadam, myśląc, że może być komuś pomocny.

Table Structure
Table_Offer
--------
ID FK
Name   
start time  
end time   

MandatoryGroup
--------
ProductId (FK to product)

MixGroup1
--------
ProductId (FK to product)


MixGroup2
--------
ProductId (FK to product)


Table_offerDetails
------------------
ID PK
offerId (FK to table_offer)
MandatoryProduct(FK to MandatoryProducts.productID
firstProduct ( fk to MixGroup1.productId)
secondProduct (FK to MixGroup2.productId)

Offer
----
 ID    name    startDate  EndDate 
---    ----     --------   ------
1       COMBO   


MandatoryGroup
-------------
 ID    name 
 ---   ---- 
1      A
2      B


MixGroup1
---------
 ID    name 
 ---   ---- 
 3      P      
 4      Q

MixGroup2
---------
 ID    name 
 ---   ---- 
 5      x      
 6      Y
 7      Z



 OfferHeader
-------------
ID  Customer count(B) B_disc time_disc  stat_disc DiscTotal orderPayableTotal
--  --------- -------  ----   --------  ---------  --------  ------------    
1     BOB     2         20      5         2             

Oferta Szczegóły ------------

 ID   offerID   MandatoryProduct     1stProduct    2ndProduct
----  -------    ---------------     ----------    ----------
1       1         A                     P              X
2       1         A                     P              Y
3       1         A                     P              Z
4       1         A                     Q              X
5       1         A                     Q              Y
6       1         A                     Q              Z
7       1         B                     P              X
8       1         B                     P              Y
9       1         B                     P              Z
10      1         B                     Q              X
11      1         B                     Q              Y
12      1         B                     Q              Z

Aby skonstruować ofertę Szczegóły, musisz podać obowiązkowe, mixgropu1 i amixGropu2. Tak więc przypadek B ma tylko 2 następujące oferty:

1 A and 2 B, 2 Q, 1 S, 2 X and 1 Z
2B 2Q and 2X =2 offers
1A 1Q and 1X=1st offer
1B 1Q and 1X=2nd Offer ... etc other combinations are vivid.

Inne oferty opierają się na logice biznesowej: Aby uzyskać zniżkę dla oferty ilościowej: utwórz zapytanie, aby znaleźć liczbę B w ofercie dla klienta. nazwijmy to QTYB. znajdź QTYB% 2 i pomnóż ją przez wartość quantOffer (która wynosi 20 USD)

Aby uzyskać statystyki, wystarczy dodać kolejny bit o nazwie EarnedPoint do zakupu klienta. i ustaw datę wygaśnięcia. zarobiony punkt może być taki sam jak zakupiona kwota. Sprawdź, czy zakupiona kwota> = X USD, wystawiają rabat zgodnie z polisą.

Podobne jest porównanie czasu zamówienia, jeśli jest to przed 17:00 i po 10 rano, a następnie wydać 5%, inaczej wydać 10% zniżki.