How to calculate Availability of Products in a rental database
Posted on 2012-03-21
I've got a big relational database that is being used for a series of companies to handle their product rentals and events.
It has gotten a bit crazy and I'm sure my calculations could be improved. Perhaps someone out there can help.
So, I've got a products table. It is just name and description stuff. Then I've got "inventory_items", which has the serial number/bar_code of the item itself and a warehouse_id.
ALSO.... there is a stock_count field. Some people have things like power-strips, that they have a bar-code on the wall of the warehouse and they just scan it 4 or 5 times if they're packing up a few of them, so I can have an inventory_item with a stock_count of more than one and a single bar-code
When people make a new order, the system creates a record in the orders table. Mostly just contact info, and the date range. (remember, this is a rental order, so there is an estimated stock_out and stock_in date range).
When they add a product to the order, it does 2 things: first, it adds that product_id and the quantity they are requesting to an order_sub table. Then, it adds the item_id (from inventory_items) and a quantity to a table called orders_sub_items.
So now, if we're making a new rental order for 3/1/12 - 3/14/12, then I do the following:
I add up the stock_count of all the items of each product's inventory_items, I'll get the total stock_count per product.
I extract a subset of orders that with stock-in/out dates that overlap 3/1 - 3/14 in any way. Then, I sum up the quantities on the orders_sub_items to determine the maximum number of each item_id rented on any given day. I then group by product_id to get the maximum amount of any given product rented on any day during that time span.
The available number of products to be rented during that period will be the total amount of stock minus the maximum number rented on any given day.
Which is fine.... until now. Now I've got to stop with the day-grouping crap, since some people will be renting the same products several times, for just a few hours, in the same day.
This doesn't even include all the exclusions for products in the maint warehouses, on transport orders, incoming new products, products to be retired at certain dates, scheduled maint, items that are in kits (where they may have a case with 20 different things in it that they rent under one bar-code, but sometimes piece out and rent separately)
Ugh..... I can't find any "standard" way to do this. There's a ton of LIFO and LILO logistics stuff for sales of products, but I need to get these ones back! So, normal logistics seem to crap out on me.