Very much new to building databases so please excuse me for what I presume is a simple question.
I am struggling to piece together my initial use case which is a promotion planning app. We have multiple defined products in a product table. We want to create promotions that are stored in a promotion table; each promotion might have different start and finish dates, different products and different discount levels. Some promotions might contain products with different discount levels e.g. product A = $5 off and product B = $20 off and some promotions might have a uniform discount level e.g. 15% off.
What I am struggling with is how I join a single promotion to multiple products with differing discounts.
One idea I had was creating a new table for each promotion “productPromotionTable” and having each product and the discounted price level in there but this would require a new table per promotion, which would get quickly out of hand (we do up to 10 promotions a day). So I am assuming there is a way to nest what I am trying to do in the one promotion table but I can’t figure out the logic.
The same logic applies to other use cases I am looking at e.g. purchase orders - each PO has different quantities and prices, customer price lists etc.
Any advice would be very much appreciated.
I imagine it would look something like this…
