Practical thesql schema advice for eCommerce store – Products & Attributes

At a Minimum You need:

1. A Promo Header Table - Holds exactly what you get with the Promotion (Free item, free shipping, $$ off)
2. A Promo-Requirements Tables (I to meany to Promo's, Hold all the requirement if the promo) Each record is one Requirement, Requirements can be "must be and item of Brand X" or must be SKU 1234, or order total must be >50$ (When i did this is had a row type flag that told me what kid if Requirement it was.)
3. A module that is good a checking if an order meets a promotion's Requirements.

Unfortunately, I can't give you an exact ERD, because the promo conditions vary so much from business to business, so it's up to you make it as complex or simple as you need. For example, do you need Promos by SKU?, By Category, by subcategory?, by brand?, do you need to exclude certain SKUs?, brands? Category's? shipping locations?

Lastly make sure you make this thing easy to maintain and adjust, because just when you think you covered all your bases the business team will come up with some new crazier promo the no customer on earth will understand how to use.

EDIT: *Now that I better understand your question here what you need:*

Package Header Table and a Line Items Table, The Package Name and Description is stored on the header, The Lines hold the Items (including the Item prices when sold as part of that package).

You need a way to adding a Package to order. When that is done add then each item in the package as a regular line item but have a extra fields to specifies that the item is part of a Package and the PackageID.

It is then up to you to decide how you want to code the order Print out, Either just print a package total, so the customer doesn't know the line item prices, or print it normally but add with the package description.

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.