Question Inventory Scheduling

[DeucedeuceNb]

This may or may not be the correct spot to ask this question, but I am wondering if anyone has made/come across and inventory scheduling database. I am still fairly new to the application and this particular project is a bit out of my league. We are trying to have a database where a user can reserve inventory items for future use. The computer also needs to keep track of available inventory count and should not be able to overbook inventory for a specific day or set of days.

For example: Today, Jim reserves 10 trash barrels for next Friday. There are only 15 barrels in the inventory. Tomorrow, John will be coming in and expects to reserve 10 barrels, also for next Friday. The computer should be aware that there won't be enough inventory available for him to make his reservation.

Just looking for someone to point me in the right direction here

Thanks

 

[spikepl]

The essential element here is booking of something or somethings and not overbooking the somethings directly or through overlap. Paul has a handy recipe for that: http://www.baldyweb.com/OverLap.htm

 

[MarkK]

Hello and welcome to the forum

This can become a very complicated problem, but the fundamental concept at play is that you never store your inventory quantities as hard numbers, you always calculate them, and your moving target is, of course, time.

A simple example is a bank account, and it's simple because a bank account is an inventory system with only one product. A transaction is a dated order with a single quantity of a single product. Obviously you can predict how much cash you will have if you post-date transactions, and you'll want an inventory system that does that. So you sum all your transactions, plus and minus, up to a certain point in time, and at that time, that is what the balance will be.

If a bank account has transactions, an inventory system has Orders, and since an inventory system has more than one product, each Order might have multiple rows, one for each type of product that will move in that Order. And your Order has a date on it, and a quantity of a product in it, so you can effectively post-date demand for stuff and run a balance inquiry for a given date in the future.

Hope this helped,

 

[DeucedeuceNb]

Thanks guys, that seems to make a bit more sense, especially looking at the baldyweb site gave me a better idea of whats going on