Assume I have a
products table with the following columns:
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
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.