I'm working on a new point of sale for a company that products for different prices depending on the product mix.
All products have a base price.
To explain my problem, I will use the following info:
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
The company has Packages, for example Package "Combo": for product A or B, if you choose 1 of Q or R and 1 of X, Y or Z you get a $20 discount.
Case A: Sometimes customers add on to a base product when placing an order, for example:
They wan't one of Product A, and they add Product Q and Product P to that to create a package that has a discounted price. Then they might add that they want 1 of product B with 1 R and 1 Z.
Case B: Sometimes customers will add 1 A and 2 B, 2 Q, 1 S, 2 X and 1 Z. According to the rules set forth by "Combo" Package, only 2 combos would apply because S is not a combo item.
Other promotions are dependent on quantity, so if you buy 2 of B you get 20% off and/or dependent upon time, it is only valid after 5 PM or before 10% off if before 10am. Another promotion might depend on when your last purchase occured or if you have purchase more than $X in Y timeframe.
My Problems:
1) How do I structure the tables so I my create the different packages or promotions in a way that is very flexible to add different types of promotions with different requirements?
2) When they order like Case B (or a mix of Case A and Case B) how do I structure my query so that I can test to see what product mix(es) are in the order, and update the prices/descriptions accordingly? Ultimately, the best result for this query would return which packages and promotions have requirements fulfilled in order of which gives the most benefit to the customer (i.e. maybe what they ordered fulfills requirements for promotion 1 and 3, but promotion 3 is less expensive. This must work with multiple promotions).
Thanks in advance for the help!
UPDATE #1
To better describe the problems at hand and to update the work done thus far to solve them I'm incluiding an ERD of the Product Model limited to the entities and attributes that affect the issue (i.e. inventory is not at play here, so no inventory entities are present).
I'm also including sample data from the entities and attributes that affect this question (to simplify reading the data, I'm putting in name/descriptions in place of Foreign Keys):
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
So, with the research and support provided by the community up until know, I have been able to solve Problem #1. In fact, I have done so with more flexibility than I thought I could do in the first deployment of the system.
All though there have been advances with Problem 2, it is not solved to satisfaction. There have been some ideas on how to do this, Neil McGuilgan asked a great question leading towards a possible solution using Relational Division (dba.stackexchange.com/questions/45829/what-is-the-name-of-this-type-of-query-and-what-is-an-efficient-example) and this book (www.amazon.com/books/dp/0471380237) helped a lot. However this solution at present, and as I understand it, only works with "one" record (combo) at a time. If a customer Walks up and says he wants 2 cheeseburgers, 1 hamburger, 1 small Apple Juice, 1 Coke, 1 French Fries and 2 onion Rings, I need a way to detect that there is only one combo in the mix and add the other products at base price. If there are multiple combo combinations, I'm looking (ideally for an sql query) that can prioritize combo combinations by largest savings.
One idea I've come up with to solve problem two is to add and attribute to PRODUCT COMPONENT flaggin the main product for the combo (i.e. hamburger). Then when running the pricing process, query what products in the order are main products in a "package", relating the query to the discount given by the PRICE COMPONENT table and ordering by that value (descending), and in that order of packages check to see if you can create a "package" with the remaining non-main products with a query and loop the process until there are no more main products or there are no more non-main products in the remainder.
Best Answer
Although its a 3 years old thread, still I'm replying it thinking that it might be helpful to somebody.
OfferDetails ------------
To construct an offerDetails , you must provide a mandatory , a mixgropu1 and amixGropu2. So caseB , has only 2 offers as follows :
Other offers are based on business logic: For quantity offer discount: create query to find the count of B in an offer for a customer . lets call it QTYB. find QTYB %2 and multiply it with quantityOffer value ( which is $20)
For statistics offer, Just add another bit called earnedPoint for a customer's purchase. and set the earnedPoint an expiry date. earned point can be the same as purchased amount. Check if the purchased amount >=$X , them issue discount as per policy.
Similar is the comparison of order time if its before 5PM and after 10AM then issue 5% else issue 10% discount.