I\'m working on a new point of sale for a company that products for different pr
ID: 651573 • Letter: I
Question
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!
Explanation / Answer
This could get complicated...
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?
You could start with a packaged_with table to determine which products can be grouped and packaged together:
package
-------
id (PK)
name
package_group
-------------
package_id (FK to package.id)
name
packaged_with
-------------
package_group_id (FK to package_group.id)
product_id (FK to product.id)
can_be_packaged_with (FK to product.id)
package_group refers to a package. packaged_with refers to products and package_groups, so that a row in packaged_with shows which products a product can be packaged with, and a package can be made up of multiple groups.
The data would look like this:
package
-------
ID | name
------------
1 | Combo
package_group
------------
ID | name
---------
1 | QR group
2 | XYZ group
packaged_with
-------------
package_group_id | product_id | 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
This will help you with the product packages themselves. I have some ideas for the rest of your question, but I don't have time to finish this answer right now...
Promotions
You list many kinds of promotions. You might want to look into some sort of rules-engine, but I'll try to keep things simpler than that, but still... This will get complicated..
Let's start with simple promotions where a price is discounted a certain percentage:
percent_discount
----------------
id (PK)
name
percent_amount
product_promotions
------------------
id (PK)
product_id (FK to product.id)
promotion_id (FK to percent_discount.id)
start_date
end_date
Here we have a table that stores what percentage you get off for product. Another table actually links products to the discount rate, and also includes a start and end date so you know if the discount is applicable on any given date.