Realtime availability by day/date

[owaring]

I have been building/maintaining a stock control system for a number of years now.

 

Part of the system allows hire stock to be booked to an order and calculating from inventory stock levels and other orders in the same time period, and reserve x amount of available items.

 

I have a working version of this that I would like to improve with the forums help.

The current version works as follows;

 

Table "Orders"

A table with orders in, and a daughter table of order items.

 

Table "Order Items"

Each order line has a qty field a start timestamp and an end timestamp

 

Table "Dates"

This table contains a record for every day in the last five years and next five years.

it also contains an 'auxiliary' field use in a relationship that I shall explain.

The dates are maintained and checked on start up.

 

Relationships as follows;

 

order items connects to dates where order item::start date ≤ dates:date AND order item::end date ≥ dates:date

a second instance of the order items table exists on the 'other side' of the dates table with the relationships;

order item2::start date ≤ dates:date AND order item2::end date ≥ dates:date AND dates::auxiliary = order item2::key

 

the auxiliary field in dates table is set to the key value of the order item record

this allows the dates table to see from the second order item table records matching the date and key of our auxiliary 

a quick calculation field in the dates table returns the maximum number of qty in records in the second order items table for each day

returning to the original order items table we can now see dates records matching the order and the maximum on each of those days

another easy calc tells us the maximum for the period

 

The problem I have with this as it stands is that each time you work with a different record in the order items table you have to update the auxiliary field in the dates table, and then return to the original order items table and perform a relookup.

 

This is fine with a couple of script triggers, but becomes slow when updating multiple item records, for instance when extending an order by a day or two!

 

I am looking to achieve this in an improved solution so that each order item line can dynamically perform these calculations.

 

I hope my english has conveyed that in a half decent manner .

I do not currently have a file with these core tables stripped back in isolation but could work on one if needed.

Ideas welcomed,many  thanks for taking the time to read.

 

owaring

[comment]

If I understand your description correctly (and that's a big IF), the Dates table needs only a few records - as many as the maximum number of days you would ever want to view at once - and two global fields:

 

gAnchorDate

gProductID

 

Each record in the Dates table would then calculate its own date as cDate = gAnchorDate + SerialID - 1.

 

 

Using a relationship:

 

Dates::cDate ≥ OrderItems::StartDate

AND

Dates::cDate ≤ OrderItems::EndDate

AND

Dates::gProductID ≥ OrderItems::ProductID

 

you can sum the total of ordered units of the given ProductID on each day.

 

 

I can't quite see the need for another occurrence of the OrderItems table.

[owaring]

Thanks comment Apart from the dates architecture this is what I have. The calculation in the dates table gets the sum per day from the second table, allowing a cal in the first table to get the max of all days This still requires setting the global auxiliary field in for each individual record, I hope to make it dynamic I'm working on a file to demonstrate...

[comment]

I didn't get this part:

 

This still requires setting the global auxiliary field in for each individual record.

[owaring]

setting gProductID in the dates table only gives us data for the ONE itemID we're dealing with.

 

Ultimately I want a calculation field in the order items record without having to change anything

[owaring]

demo.fp7.zipdemo file

[comment]

Well, you could have a "stock" of records in the Dates table for each product in the Products table (you do have a Products table, right?). Otherwise you'd have to script the checking process.

[owaring]

not sure if I follow... I do have a table of available stock levels for each item yes

[comment]

I meant something else. Suppose you have 4 products in your Products table, and suppose an order cannot exceed a period of 10 days. In such case, you would have 40 records in your Dates table (needs a better name, actually). Adding a new product would necessitate adding 10 new records to the Dates table, too.

 

 

That said, I don't see why you wouldn't script the entire checking process - after all, it only needs to be performed at the time an order is being finalized, doesn't it? A nested loop over the dates and the products shouldn't take long. IMHO.

[owaring]

Thanks again.

 

Having a field in the dates table for each item will prove difficult as a modest system offers 300 items for hire, as well as the facility for items to be added or removed by the users.

 

The current system is scripted on a per item record basis using script triggers but there are a number of scenarios where adjusting the quantity or type of item on one line has multiple effects on line items in other orders. e.g. increase/decrease of qty, item change, date change, job cancelation

 

The efficiency of my current version, along with the prevention of erroneous calculations has lead me to want this 'dynamic' solution.

 

 

As always comments, suggestions and solutions welcomed! 

[comment]

Having a field in the dates table for each item will prove difficult

 

No, no, no, no - that's most definitely NOT what I suggested. I was speaking of having a RECORD for each date/product combo, with one field for the date and one field for the ProductID.

 

In any case, it's not like you have an abundance of choices here. Clearly, you must check each booked item against its sibling bookings, and you must perform such check independently for each date of the order. IOW, if someone orders 3 different items for a period of 6 days, the system must perform 3 * 6 = 18 independent availability checks. If these checks are to proceed without any scripting (and I am not saying that is necessarily the best approach), then there must be enough records already in place to facilitate them.

 

 

 

there are a number of scenarios where adjusting the quantity or type of item on one line has multiple effects on line items in other orders.

 

This part doesn't sit too well with me, I am afraid. I would think each order independent of its sibling, except for creating (or removing) a conflict of resources.