Inventory System Design for Rental Company

Posted on 2007-10-16
Last Modified: 2013-11-12
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.
Question by:Danielcmorris
    LVL 8

    Accepted Solution


    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
    LVL 4

    Author Comment

    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.  
    LVL 8

    Expert Comment


    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.

    LVL 4

    Author Comment

    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.

    LVL 8

    Expert Comment

    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.
    LVL 4

    Author Comment

    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.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Software development teams often use in-memory caches to improve performance. They want to speed up access to, or reduce load on, a backing store (database, file system, etc.) by keeping some or all of the data in memory.   You should implement a …
    Introduction A frequently used term in Object-Oriented design is "SOLID" which is a mnemonic acronym that covers five principles of OO design.  These principles do not stand alone; there is interplay among them.  And they are not laws, merely princ…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now