Database Schema for Pricing Products (packages, promotions, qty based, limited time offer...)

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!

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

Here is a link to a flow chart giving an example of a combo, a fast and visual way to understand the table structure.

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.

Answer 1

Although its a 3 years old thread, still I'm replying it thinking that it might be helpful to somebody.

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             

OfferDetails ------------

 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

To construct an offerDetails , you must provide a mandatory , a mixgropu1 and amixGropu2. So caseB , has only 2 offers as follows :

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.

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.

Answer 2

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.

Ideas for the other promotions coming later...

Answer 3

If you are looking for a solution that can handle all scenarios, I recommend this design:

Groups [id, name]
GroupItem [groupId, productId]

Offers [id, name , offerTotalPrice]
OffersItems [offerid, itemId, isGroup, quantity] 

if isGroup = true this mean this item is a group item. if isGroup = false this mean it is a product item.

Complex Offer Example: if you buy 2 of Pepsi Soft Drinks with one chicken burger and 2 salads you will pay only 10$.

Products
--------------------------------
id  |name
--------------------------------
1   |pepsi
2   |7up
3   |meranda
4   |chicken burger
5   |salad

Groups
---------------------------------
id  | name
---------------------------------
1   | "Pepsi Soft Drinks"


GroupsItems
---------------------------------
groupId | productId
---------------------------------
1   | 1
1   | 2
1   | 3

Offers
-----------------------------------------------
id  | name          | totalPrice
-----------------------------------------------
1   | "Special Combo Offer" | 10$



OffersItems
------------------------------------------------------
offerId | itemId    | isGroup   | quantity
------------------------------------------------------
1   | 1     | True      | 2
1   | 4     | False     | 1
1   | 5     | False     | 2