Inventory Management -- Building Your Own Database

By Tim Sanders

March 13, 2003

Are you a WISP tired of not knowing where your radio gear is? The answer is inventory management, and the ultimate way to control your list is to build it yourself.

You're a wireless Internet service provider (WISP) and you're tired of not knowing where your gear is. You have radios in installation trucks, at customer sites, relay sites and in buildings. You have inventory in your office, at employees' houses and packed away in trade show kits. In short, your stuff is all over the place and you're not certain where. Likely these units are not really lost. Most are deployed to paying customers or serving as valuable backup stock.

Still, exact locations aren't certain. Knowing which serial numbers are where is out of the question. Also, accounting wants to track the capital equipment value precisely each month. You have a problem and you plan to do something about it. So how do you go about fixing your inventory tracking system?

The Options

If you have radio inventory you are likely already tracking it some way. Some WISPs literally keep track of equipment with spreadsheets, word processors or on paper. This can work. However there are two main drawbacks. It is labor intensive to maintain, and errors creep in. It is hard to automate data entry with spreadsheets. Each transfer increases the likelihood of garbled cell entries. Companies sometimes write scripting to automate portions of the process. However, this effort provides minimal gain for the cost. Another drawback is a limited audit trail with even highly scripted spreadsheets.

Companies can purchase an inventory-tracking program. This can be a great solution. Commercial software offers full-fleshed inventory management, reporting and audit capabilities. Unfortunately it can be quite expensive. Also, adapting it to other software can be difficult and more expensive. Few firms write inventory management programs tailored to the WISP marketplace. The WISP has unique business needs. There is a learning curve in nomenclature and process for employees. Additionally, WISP operations create tremendous pressure to transfer inventory quickly and often. Troubleshooting by switching out problem equipment is efficient. Tracking frequent transfers with commercial software is not always as efficient.

Many WISPs choose to build their own databases. This offers the advantages of a program tailored specifically to the businesses' needs, familiar terminology and incremental payment options. Businesses can customize modules to address their own operational methods. With planning, costs are modest and result in a valuable piece of software.

For WISPs leaning towards this option, there are several key considerations.

Accountability

Remember that inventory management is not just software. It is a process, in part a physical one that must permeate the organization to succeed. It requires discipline and accountability in access to inventory, entering data and reconciling discrepancies. Some suggestions are to:

  • Assign one person to enter the information for new incoming stock.
  • Task the same person to verify the inventory transfers logged by the staff.
  • Lock the stockrooms and limit access.
  • Use a paper system to track quick transfers with basic information such as staff member, item, serial number, date and location deployed or recovered from.

These crucial elements spell success for the system. Simplify it enough for everyone in the organization to understand and follow.

Planning Comes First

Building an inventory tracking solution with lasting value requires a few crucial elements.

First spend time and resources on planning. Companies face strong temptations to write something quick only to be frustrated by its limitations later. The opposite problem is more debilitating. Companies bog down trying to make the database do everything, creating paralysis and cost overruns. Decide on your objectives early and stick to them.

What do you need the database to do? Determine needs and stick to them when you begin actually writing the code. However, before writing code, spend time considering how your business will change over the next few years. This doesn't mean to engineer the database to handle the future. Rather, focus on the types of information you are likely to need to track. This will be incorporated into the table structure.

However, before we cover table structures, it makes sense to describe the basic elements of database design and how to apportion development money and resources to each aspect.

Elements of a Relational Database

However a WISP proceeds, it will likely choose some type of relational database, such as MySQL, MS SQL or MS ACCESS. All of these database forms share common elements such as:

  • Tables -- Most critical to spend design time on.
  • Queries -- The most long-term time consuming element.
  • Forms -- Important for user efficiency.
  • Reports -- The distillation of information.

These are listed in a loose order of importance.

The Table Structure is Key

At the base is the table structure. The tables contain lists of the various types of information a WISP will need to track, segmented into manageable, non-redundant, thematic tables. Some examples are tables of contact information, inventory types, States, cities and other information. The more efficient and clean the table structure, the faster and more flexible the overall database can be. This is the most critical portion of the database design. It is hard to re-design table structure once you're in operation

Stop here to consider your business in the long term. Imagine the types of information necessary to track future operations. For example, if your firm plans to someday link customer location with mapping software, tracking latitude and longitude coordinates will be necessary at some point. Make provisions for this. Likely fifty percent of the effort of a database design is spent here. The table design is the one element that should not be built in stages, but others can be.

Building in Stages

Once an established table design is in place, additional elements of an inventory-tracking program can be added in stages. The query system provides the foundation for data mining. It also fuels most of the form design. Complex query outputs are typically built from multiply nested queries. There are a number of different types of queries that can massage data in various ways. However, it is not necessary to pull every piece of information possible out of a database right off the bat. Keep the first version simple and focus on building clean, easy to use forms that will enlist employee buy-in to the change.

Keep Data Entry and Transfer Simple

The form design portion is very important to the success of the database's adoption. If it is clunky and cumbersome employees will avoid it. However, do focus on collecting as much information as possible even early on. The queries necessary to do complex tasks with this information and the reports they can generate can wait until latter stages. A WISPs best strategy for a first database version is a simple form and report structure.

A big impediment to success with any inventory-tracking database is simply employee acceptance. Simple is best. If it is easy and simple they will use it. If not, it will quickly become a pricey footnote of failure.

Tim Sanders is founder of The Final Mile, a fixed wireless consulting group. His experience was gained running a multi-state fixed wireless ISP. He can be reached at www.thefinalmile.net

Originally published on .

Comment and Contribute
(Maximum characters: 1200). You have
characters left.