Database with multiple objects

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…

Hello @Damien_Green

Glad to see you in our community.

Can you clarify, are you familiar with Relations in Data Base context?

You can create a related column(1:N) Products in the Promotion table, which will link one promotion and many products.

Regards, Dima.

Hi Dima, I am somewhat familiar with relational databases. The issue that I have with what you have suggested is that for each promotion we need to know product and promotional price; I can’t store the promotional price on the product table as every product is a member of multiple promotions.

I know there is something very simple that I am missing as the same complication exists in many use cases for example a purchase order references the product table but each time we order a different quantity and pay a different cost for the product.

Hello @Damien_Green

Perhaps this option will work.

Elso you can create the data view for better visualization