Database Design for Product Promotions

Question

Assume I have a products table with the following columns:

• id (PK)
• category_id (FK)
• color_id (FK)
• material_id (FK)
• seller_id (FK)
• size_id (FK)
• name
• price

Further assume that there could be 0 or more promotions that apply to a product. A promotion will have a start and end date / time, a discount amount (e.g. $100 off) or percentage (e.g. 10% off), etc.

The promotion will apply only to products that match a certain criteria, such as:

• all products that are "red" color and "large" size
• all products that are under the "shirts" category
• all products sold by seller_id = 1 and price > $50

How would I represent this in a database? I feel that I need to associate one or more "query string" to the promotions table (e.g. ?color_ids[]=1&sizes[]=3) and test each product against all query strings to see if that product is part of that promotion. I expect this to be very inefficient, though.

Another option is to have a table products_promotions that gets set whenever a product or promotion is inserted / updated in the system, then it's just a matter of checking that table to see if the product has any promotions associated with it.

Thoughts?

Answer 1

One way is to create a table promotions as:

• id (PK)
• start_date
• end_date
• discount_amount
• discount_percentage
• condition

To determine the promotions the product qualifies for, you would need to dynamically execute all the queries, and select sum(discount_amount) and sum(discount_percentage). Here is a snippet that can help you get started:

SET @SQL = CONCAT('SELECT id FROM products WHERE ', @condition, ' INTO @product_id');
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

You can put this snippet into a function that takes condition and returns product_id.

Running all those queries every time you SELECT a product will probably end up very time consuming. So you might want to create a table product_discounts, with two foreign keys to both products and discounts, to facilitate the many-to-many relationship between these two tables.

Then you insert and delete rows from product_discounts using either events (daily recalculation of entire relationship) or triggers on the two tables, or a combination of events and triggers.

I hope this helps.