Reserving & querying availability of inventory: how the heck?

March 12, 200718 yr

I'm really stuck on this question of reserving & querying availability of inventory.

Say I have ten of a certain kind of chair in my inventory.

They will be rented at various times, in various quantities in the future, and then returned to inventory.

And then, of course, I have a warehouse full of all different kinds of furniture.

I have got the inventory part, creating and adding multiples of the same item.

I can relieve inventory (shipping) and return to inventory.

But the part that has me stumped is creating a DB I can query that tells me whether that item will be available on any given day in the future. It seems there would have to be a record for every piece (Y) - and then a reservation record (X) (with a field saying yes or no available for that day) for every day in the future for each Y record. So that, for example, if I wanted to track two years ahead, there would have to be 730 (2X365) X records for each piece (Y) of inventory. And if I had 10 of those Y items, that would mean 73,000 records. Yikes! There must be another way.

Makes my head hurt ! Thanks in advance for any suggestions....