Modeling advice: How to build item level discounts for set time periods
Looking for help and advice on how to build out a discount function in our model.
My company manufactures products and sells them to stores, for this example let's say our customers are Target and Walmart.
Our business will give special discounts on specific items (~10 items) for specific time periods, like May 2nd through June 5th. They also might extend the discounts, so that 5/2 to 6/5 discount is now 5/2 to 6/19. These discounts will be for a specific customer and specific items (we have a customer list and an item list in the model). The discounts seem to be $5 off each item for the time period, but I want the flexibility where it could also be a percentage discount.
Our sales order data is pretty straight forward, each line will give information of the item sold, the customer, the transaction date.
Ideally, we could have a system where a sales manager comes in and enters in the discount, but for now will be the model administrator.
How would you build this out? Target has a $5 discount on items A B C D from 5/2 to 5/19, Walmart has a 5% discount on items B C D E from 5/5 to 6/2.
My thoughts/confusion: I'm not sure it makes sense to have some sort of boolean in the sales order module to detect if there should be a discount, bc we'd have to keep adding line items (example: if Date is XXX, customer is XXX, item is XXX). I am not sure if we need to basically have a list of discounts where you add a list member and then connect it to a customer and items over dates. Maybe we have a boolean check for a specific discount thats added to a discount list where you check off the customer and the items, then calculate the sales for the date range (boolean check the dates?) and then calculate out a total rebate amount? I am not sure.
Would love some help or thoughts, thanks!