Inventory System Design for Rental Company

I've been building a system for a client who sets up the AV systems for hotel banquet halls and event rooms.  They've got about 100 offices that handle about 150 hotels.  Each hotel has about 5 rooms that are booked an average of 5 times a week.  For each event they have a team pull LCD projectors, cables, speakers microphones, screens, etc... out of their local office inventory.  Anything they don't have they pull from one of the other offices or a shared regional warehouse.  
sound fun yet?
So, right now I've got it set up so the user logs in, books and event, adds an item to the order, adding it automatically locks the number of items in stock for the event - individually.  So, if they need 2 projectors, they get projector serialnumABC, serialnum123, leaving serialnumXYZ available.  Now, if they need more than they have, it goes to a "hot sheet" which shows up on the front page of all the warehouse managers' system (of those warehouss associated with that office).  The warehouse manger has to allocate their products to the office for the order and produce a pullsheet for that office for that day, and lock down those inventory items for the period of time that the borrowing office needs them for....  ok.... brain freeze.

Now, I've done all this in pieces.  Basically, the client started with 1 office and no real inventory issue since they only really had a few things of value.  Next, 5 more opened, so they kept track of the items that were shared in their heads.  Then about 10 more... the manager started keeping an excel sheet and I simply pulled it into the system.  then they purchased another company and dropped 120 more *$&#@^@# offices on my system.  Now I've had to add all sorts of crap to their system and I feel like I'm wading through piles of duct-tape and coathangers everytime I need to make a change.
Where can I find REAL information about how this sort of system is SUPPOSED to be made.  What I've got is simply insane and, if I get hit by a bus, my assistant (or another developer) is never going to figure it out.  Any link to seriously detailed literature or diagrams would be helpful.
Who is Participating?
Kelvin81Connect With a Mentor Commented:

I believe you may be talking about an ERP (Enterprise Resource Planning) tool.  You want to

Check out these links, they may help or at least point you in the right direction of how you'd like to structure your application.

Here's some Open Source ERP code maybe you can take some peices
DanielcmorrisAuthor Commented:
That looks pretty interesting, but I'm actually looking for some more design specific material.  Something like how you need a product table, then a catalog table to manage the active products, then put xyz type of records in another table etc...

Maybe even someone who has posted a database diagram.  

take a look at that.  I don't think too many people have designed a system that complex from scratch all by themselves.  Your talking about a HUGE question.

That link has some pictures to some Database schemas and some layouts for systems.

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

DanielcmorrisAuthor Commented:
The system I'm working with is designed from scratch - over the course of 5 years - and used for multiple clients over that time.   Now it's really getting out of hand.  I'm pretty stuck.

A real commercial ERP system is rather pricey and will take a year to install.  Once they've got it, they're stuck with it.  Worse yet, their company has grown due to their ability to be flexible.  If one office needs something, we can make the system deal with it.  A commerical ERP system takes that flexibility away entirely.  ANd it costs a fortune.  - so its up to me and my assistant.

A while ago I did a customer assistance system.  After a lot of design work we finally came up with a good process of task managment.  Basically an inventory of problems that got passed from one desk to another.  Once we got theory of the process defined, it was much easier.  That's kind of what I need.  

I suppose I'm looking for something like how there are definitions of FIFO inventory managment processes.  I need something similar, but in an inventory system, there is no item-level tracking, and when the widget leaves the warehouse and goes to the customer, it's over.  In our case, it bounces back and forth over and over again.

Sorry mate,

was just trying to provide you a couple resources that you could take a look at so that you could maybe use in your spreadsheet.  I don't have any experience creating inventory management processes.
DanielcmorrisAuthor Commented:
Ok, here's what I ended up doing:
I have an orders table that holds all the detailed info about the client's order.  Then I have an OrdersSub table that holds all the product, quiantities, prices, and warhouse ID of the products ordered.  
I also have a products table that holds all the detailed info about the products.  Then I have and inventory table that holds warehouse_id, each item's serial number, giving it a unique id.

Finally, I have an ordersSub_items table that holds a list of the warehouse inventory items and their associated ordersSub id numbers.

So now, to get outstanding inventory I can join the warehouse inventory table's unique IDs with the ordersSub_items table, then filter by the orders within the selected open/closed date.  To get the available of the product I then subtract the count of the productID within the selected warehouse and subtract the outstanding.

This way I can track the individual items rented to each client while allowing the employee to search for products that will be available on selected dates.  Blech.

Initially, I had hoped to use a simple FIFO type of system using an allocation table that could be joined with the inventory, but that only gives me current inventory - or history, if I keep a log.  It also doesn't specify by individual serial number, just counts of widgets.  This format was the only one that I found in any ERP systems.  Even the systems I designed for Old Navy and the Gap didn't track the actual item (although with 44,000,000 records each day tracking only style-color-size, not individual items, that started to take up a bit more space that could be efficiently used)
Of all the systems I've had to design, I think rental systems have to be the most irritating.  Even things like ATMs are pretty basic (although international currency exchanges are a pain in the butt).

In any case, if anyone has any better theories of how to handle this sort of thing I'd be interested in checking them out.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.