Database Design for Product Promotions
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.