Advanced Coupon system advice

Hi, I already have a "gift voucher" system in place on an existing website...but client now requires an additional "coupon system" thats seperate so they can create special promotions that may include certain items to be $$$ discounted, or shipping to be free for a week, or a particular item % discounted for a 3 days etc etc, or all items in a basket to be discounted in some way.

I have created a potential table in the database to hold the following fields:

1.Coupon Code – create a unique code to give to your customers.
2.Coupon Value - sets the value of the coupon in pounds (£)
3.Coupon Shipping – stores a binary value to state if the shipping cost is effected by the coupon, and then will base shipping discount on either the value stored in points 2 or 4.
4.Coupon Discount - sets the value of the coupon in percentage (%)
5.Coupon Type – sets the coupon to be used for total products in the shopping cart OR to only effect one particular product or category of your choosing and then uses either the value stored in points 2 or 4 above to discount.
6.Coupon Category – stores the category ID value for use of the Coupon Type if set to categories.
7.Coupon Product – stores the product ID value for use of the Coupon Type if set to products.
8.Coupon Used - each time a customer uses this coupon, then the value here will be incremented so you know how many customers have used this coupon.
9.Coupon Valid From – sets the date from for the use of an expiry OR to allow it to be used for a particular day if same as valid to.
10.Coupon Valid To – sets the date to for the use of an expiry OR to allow it to be used for a particular day if same as valid from.
11.Coupon Status – activates or deactivates the coupon if you chose to temporarily take it out of use.

so for above for example i would store the product category in this record if the coupon discount will ONLY effect a particular category on the website..so theres lots of flexability for the client to create all sorts of discounts for their products.

I just need help on creating the various ecart trigger rules. if you could give me some guidelines on that and of course if i am missing anything from the above table.

The coupon system (together with the gift voucher system AND the International shipping system will ALL take place on the websites CHECKOUT form so session variables would be set as soon as the customer filles out all the address details etc and then clicks on PAYPAL the paypal button.

Chris

The table structure looks like it would work,

I think you would need to create 2 rules for each type that can be defined in the Coupon Type column, 1 for it the coupon is a dolor amount, and the other for if the coupon is a percentage.

for the dolor amount coupons the calculations will use the Flat rate

for the most part the triggers will use the If today is after the Coupon Valid From date AND before the Coupon Valid To

if the coupon applies to all products, the calculation will use the based on cart subtotal if its as percentage.

for the specific items or category coupons, the subtotal of items with a specific column value triggers will be used.