I am grappling with a database design problem and wondered if anyone has any better ideas of approaching it......
I am working on a database that holds a pool of stock (one table) of objects (another table). (The products are branded signage that are used for events.) One object has more than one identical physical item in stock, the object record describes what the item is and the stock records are the individual physical items.
When the sales person selects a line item on the sale, my database generates a record in a table of stock requests that specifies what it is (object) and when it is required from and to. The idea is that it can then work out how many items of stock are required simultaneously and decide if there is enough in the pool of stock to fulfil the requirement.
So, this is where I'm up to:
From the objects table I set two global fields with the start and end date I am looking for.
I have identified four potential combinations of stock requirements that I need to include and have a table occurence for each. They are:
• A requirement that starts before the period and ends within it
• A requirement that starts within the period and ends after it
• A requirement that starts and ends within the period or on the same days as the period
• A requirement that starts and ends outside of, but includes, the period.
I then use a script step to add the number of requirements in each of the table occurences together to give me a total quantity of stock required within the period I'm testing. I have this working perfectly except for this problem:
If one requirement within the period finishes before another one starts they count as two different requirements. However they should only really count as one as the same item of stock could be used for both.
The only other way I can think of doing this is to divide the time in the period into slices (say one slice per day) and loop through calculating how many requirements exist for that slice then take the maximum of those quantitties. However, this will be messy, performance sapping and the client has mentioned wanting it accurate to times as well as dates that would require thousands of slices per day!
Am I missing an obvious alternative way of doing this?
Thanks in advance for any ideas.