0

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?

  • 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
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.