Database Design for Product Promotions 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&amp;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?
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.