hi there,

my client wishes to be able to create coupons/discounts for customers who have purchased from their store.

possible scenarios...

1. discount off total purchase if over certain amount
2. discount off total purchase (any amount)
2. free product (eg. socks) with purchase (if over certain amount)
3. free product (eg. socks) with purchase (any amount)

the socks or any other items would already be in the main products table.

the customer would receive a unique code via email and enter it at the checkout stage.

i am a bit stumped as to whether i should be looking at one set of tables to handle all the options above, or whether i should create to distinct sets to handle either a discount scenario or a free product scenario.

any advice on this would really be appreciated.

mike

I would suggest a discount scenario and a separate free product scenario. This would allow you to set thresholds and in the case of free products relate to the product table.

I'd suggest a discount rules table defining the trigger level, with 2 child tables defining what those rules are. (type 1 are percentage discounts, type 2 are gift)

ok, my coupon/promotion module has become quite tricky...

a promotion can be valid for:
- any purchase
- a specific cart amount
- a specific product
- a specific product group (eg. jerseys)

the user may receive:
- a discount off their subtotal
- a FREE product (must be in the online catalogue)

what i have so far is the following tables...

promotions
----------------
promotion_id
promotion_uuid (unique code for purchaser to enter)
dateCreated
dateExpiry
promotion_title

promotion_criteria
-----------------
pc_id
pc_title (eg. Product Category, Product or Cart Value)

promotion_criteria_value
-----------------------
pcv_id
promotion_id
pc_id
pcv_value (eg. product ID 1, $300) INT

promotion_type
---------------
pt_id
pt_title (eg. Product, Discount)

promotion_type_value
---------------------
ptv_id
promotion_id
pt_id
ptv_value (eg. product ID 2, 30% discount) INT

have i tried to normalise this a bit too much ? am i missing anything at this stage.

any help would be appreciated.

mike

hmmm... just thinking, i might need to include a column in the promotion_criteria and promotion_type tables that indicate what the value type would be eg. integer, decimal, percentage ?

mike