How should I store types of coupons in my db?

I'm creating a coupon system with many different types of coupons. I'd like to structure it so it can be extended. I'm not sure the best way to store the different types of coupons, for example:

• Fixed amount off entire order
• Fixed precentage off entire order
• Fixed amount o开发者_运维知识库ff one item (could be by sku of item or could be most expensive item)
• Fixed percent off one item (could be by sku of item or could be most expensive item)
• Buy x get y
• Free product (by sku of item, by price)
• x for $y (3 for $2, 10 for $15, etc.)

I'm using mysql, would it best to store as an array? json? Any other ideas or people with similar issues care to share how they did it?

Off of the top of my head you could have tables designed as follows:

Table: Coupon_Summary
Columns: Coupon_ID(primary key), Coupon_Name

This table will hold 'top-level' data about the Coupon.

Table: Coupon_Description
Columns: Coupon_ID(foreign key), Coupon_Description

This table will hold the description of the coupon.

Table: Coupon_Value
Columns: Coupon_ID(foreign key), Coupon_Value, Coupon_Currancy

This table will hold how much discount the coupon offers. Coupon_Value can be a percentage or a hard value(percentage will be appended with a % sign), if this is zero the coupon offers full discount, or the item is free in other words. This also includes the currency to base the discount amount off of, so that you can do conversions between currencies.

Table: Coupon_Target_Order
Columns: Coupon_ID(foreign key), Order_IDs

This table holds data related to which Order the coupon effects. If the Order_ID is null or zero, the coupon is valid for all orders. Otherwise you can have multiple IDs for multiple orders.

I hope this was of some help =).

I would create another table - tblCouponType for instance and populate it with a unique numerical and string for notes of the types I have, and add to it as new types become available. Then add another column to your coupon table that references the numerical value of your coupon type. This helps with the whole -"Relational" part of the database:)

I assume you have some sort of products table that contains all products you can sell. You can create a coupons table that looks something like:

id       discount       discount_percentage
INT PK   DECIMAL(10,2)  DECIMAL(3,2)

1        5.00           NULL
2        NULL           10.00

Then you could create a link table coupons_products like this:

coupon_id   required_product_id
INT FK      INT FK

1           4773
1           993

So in this example, coupon ID 1 gives a $5.00 discount and requires two products to be present on the order: 4773 and 993. Coupon ID 2 gives a 10% discount and requires no products.

With SomeSQL, I'd rather not use JSON for it is nearly impossible to efficiently query it.

My Approach would Be, simplistically speaking, to have one Table for Coupon types (columns: id, name, ...) and another one for The actual coupons.

In The coupon Table, I would have a column "type_id" Cross-referencing to The "couponTypes" Table (or a Foreign Key, for that matter).

This way, you can always add new Coupon types later on without invalidating The Data you had to this Point.

Querying "Coupons by Type" is a matter of

"SELECT 'id' FROM couponTypes WHERE 'name' = 'fixed sum'"; => $id

"SELECT * FROM coupons WHERE 'type_id' = $id"; => All fixed sum Coupons. 

Welcome to MySQL!