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.


  • How do you plan to use your promotion data? Variant 1: promotion is an additional attribute of product, insert/alter promotion (including its time expire) initiates recalculate to mark all products it is applied to (only one promotion can be applied to a product), product insert/alter initiates recalculate to obtain the promotion applied to it if exists; Variant 2: access to a product initiates searching the promotion applied to it (on the fly). – Akina Mar 14 at 6:51
  • Isn't that what my question is? And aren't your Variant 1 and Variant 2 the two possible options I gave? – DatabaseNewbie Mar 14 at 7:31
  • Isn't that what my question is? No. Your variant 1 looks like my variant 2, but I cannot understand does you want static or dynamic realization. Your variant 2 is similar to my variant 1 without restriction that only one promotion can be applied to one product. – Akina Mar 14 at 7:36
  • I guess my question goes to "static vs dynamic" realization - and how to implement them, what their pros and cons are, etc. – DatabaseNewbie Mar 14 at 10:36
  • I guess my question goes to "static vs dynamic" realization Static realization eats resources while insert/alter, dynamic realization eats resources while selecting. Think when it is more safe. I think it is better to choose a static version of the implementation - because insert/alter promotion and select data are interactive, and it is better to optimize them for to decrease the time which client spends, whereas insert/alter products (I think) is bulk and continuous, so its time increase is not so critical (if this process do not lock another processes, of course). – Akina Mar 14 at 10:51

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');

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.

Your Answer

