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.
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!
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.