I have a database table with a range of products each have a price dollar value.
I need to load a ‘sale’ into my sales table, each row has a name, start and end date. Then if between the dates the discount is applied where appropriate.
I was doing to add a column percentValue and dollarValue where it can be one of the other, or have a discountType 1=percent, 2=dollar then discountValue being percent or dollar. So if 2 and 20 it’d take $20 off the price when displayed on the screen.
Reading through my requirements. I need to apply to all or selected products.
I want to have to have a table SaleProducts JOINING ProductID and SaleID together. Therefore seeing if a sale is linked to a product which may be all.
Finally. My issue.
How can I do it so the discount can be applied to each product individually?
I thought of adding columns discountType and discountValue into SaleProducts?
But what if I load a new years sale now and I later add new products do I have to then go link them?
Looking for ideas.