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 andprice
> $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?