Design – How to store product and pricing history in a product database

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.

In the past, what I've seen at companies that do this is one of two approaches:

1. A "bundle" is a separate product. It's put into the database as its own product, stored in inventory as its own product, and gets removed from inventory and shipped as its own product. No refactoring required, but ties up product in inventory that could be sold individually, if it was broken out. It has the virtue of being customizable as a bundle; i.e. the cost can be different than the individual products.

2. A "bundle" is a "Bill of Materials," culminating in a finished product. Requires a completely different approach, and much refactoring, but preserves the integrity of individual products on shelves. There would be lots of duplication, since each individual product still needs its own bill of materials containing one item (itself).

If I were doing it, I would find a way to sell the idea of bundles as "quick-entries." It works sort of like a GUI macro; you select a bundle from the list of products, and it automatically adds the necessary individual line items (and quantities) to the invoice. You still need a "bill of materials" to implement it but you don't have to refactor the entire system.