Defining the Problem

The Graphic Design Institute, a fictional company, sells trade show materials, including graphics and hardware that can be used to construct booths and displays. As the company grew from a small startup with a dozen full-time employees to a larger player, the regular paperwork involved in tracking sales and inventory purchases became impossible to manage. Eventually, the sales team standardized on using an Excel template (which was distributed around the office) to record new sales. These sales records were typically filled out in the field on a notebook computer by traveling sales staff. When a new sales order was completed, the sales associate would e-mail the spreadsheet to a sales manager at the Graphic Design Institute, who would add the information to an Access database using a Visual Basic macro program developed in-house. Then problems began to appear.

Here are some of the problems the Graphic Design Institute found with their ad hoc system:

• More than one employee needs to use the data in the Access database file. For example, different individuals use it to create sales projections, determine the inventory requirements for next quarter, and draw up monthly performance bulletins. Unfortunately, even though the database is hosted on an internal network, it cannot reliably support concurrent users. As a result, the company periodically distributes separate copies to individuals who need the data, resulting in additional coordination and distribution headaches.

• The sales template fluctuates regularly. Each version has a separate Access module coded using Visual Basic for Applications (VBA). Determining which version to use and validating the entries is a difficult and error-prone task.

• As the sales force expands, the regular work of inspecting the sales e-mail messages, saving the attachments, and running the Access module has become very time-consuming, and as a result the inventory supply isn't keeping up with demand. The sales force is currently up to about 100 individuals.

• The sales database is growing extremely large and is performing poorly for the aggregate queries needed to generate historical reports.

• The sales employees are demanding a way to retrieve information about the sales they submit. Unfortunately, they currently have no choice but to query the sales manager directly.

These growing pains are typical of an expanding company that's trying to evolve from a patchwork of simple stopgap solutions to an integrated system. Management has decided that the current year's budget doesn't allow for the development of a traditional end-to-end proprietary solution for data management. However, management is interested in a solution that might naturally evolve into a more customized system in the future.

Key Analysis

Some of the problems facing the Graphic Design Institute are the result of the company outgrowing its current system. At this point, it's clear to all involved that Access can't scale to store the large amount of data required or serve the multiple users who need to access the data. The Graphic Design Institute needs to start by installing an enterprise database engine (in this example, Microsoft SQL Server) and building a new table structure. This is the most time-consuming step, but it won't necessarily involve a large expense because the sales employees won't need to access the server directly. Instead, the Graphic Design Institute only needs to add licenses for the server-side components, which will connect directly to the database as needed.

Beyond the upgrade to SQL Server, the problems and the possible solutions become a little more complicated. There are two important points about the way this system is used:

• The sales employees perform their work in a disconnected state, without necessarily having a network or Internet connection. Therefore, the solution must allow the completed invoice to be submitted after it is created.

• The sales employees don't receive any immediate result or response when they submit their orders. This means the server doesn't need to process client requests synchronously. Instead, it can take full advantage of asynchronous processing and handle the work when convenient.

Finally, the upgradeability of any solution is a key concern. Many of the problems that the Graphic Design Institute is experiencing are a direct result of not having planned for the future. In crafting a new solution, it's all too easy to meet the current needs by creating a system that will be just as difficult to enhance as their current one.

Although Excel will remain the sales reporting tool for the near future, it would be a dangerous mistake to assume that it will always be the best approach for the Graphic Design Institute. In the future, the problems inherent in using Excel to create orders (such as the lack of strict validation at the time of entry and the difficulty in managing new versions of the custom spreadsheet) might require the development of a proprietary order-entering client. If the new system is designed using server code that is tightly coupled to Excel, this enhancement won't be easy. In our solution, we'll consider carefully how the various parts of the system can be separated and what future improvements can be made without requiring a full rewrite.

Evaluating Different Paths

The disconnected nature of this system is the most significant factor in determining how to architect the solution. In Chapter 8, we considered using message queues for this task. In this case, message queues aren't a viable option because they require a dedicated client program, which the Graphic Design Institute isn't ready to develop or support. In addition, message queues aren't included with older Windows operating systems, and the Graphic Design Institute isn't prepared to tackle the effort required to upgrade or reconfigure sales notebooks. Only one assumption can be made about the notebooks used by the sales staff: They support e-mail and basic Internet access. Therefore, an ASP.NET front end is an ideal choice. However, ASP.NET would require the sales staff to be connected to the Internet at the time of order entry, which might not be practical. To overcome this limitation, we can use an ASP.NET page just to upload the completed orders, not to enter and process them. This fits with the Graphic Design Institute's plans to stay with the Excel format for the present.

Of course, the ultimate solution needs to be implemented in separate stages development, deployment, and testing. Here's a recommended migration strategy:

1. Replace the manual steps that the sales manager currently takes to add the information into the Access database. The new process should also be implemented generically enough that new spreadsheet versions can be created without causing confusion or requiring a recompile.

2. Provide a consistent way for order files to be transferred from the sales notebooks to the central system. Ideally, this transfer method will bypass the sales manager completely unless a problem occurs.

3. Consider some future enhancements, such as creating a dedicated Windows Forms front end and moving validation to the client.