The Shortest Distance from Prototype to Production
By John Neil

A case study in how the PHP/Oracle development Model reduced the application lifecycle at Myers Internet.

Among the core business functions for companies that are driven primarily by an accounts receivable business model are entering, tracking, and billing orders. Companies that do a good job of this can scale their organizations and increase their profits without encountering infrastructure limits. When order processes are cumbersome, error-prone, or inconsistent, companies lose money through direct costs and reduced productivity.

At my company, Myers Internet, the core business issues revolved around building a client base, providing Myers with ongoing services, and helping it solve client issues as they arose. The company was using many different systems to handle various aspects of the order entry and fulfillment cycles. These systems were neither integrated with each other nor equipped with a mechanism for ensuring that every order resulted in a correct billing.

The Myers Order Tracking System (MOTS)

Like many other organizations, Myers had grown from a small to a medium-size company while maintaining the same processes and systems throughout its entire growth. Most of these processes were built when all the transaction processing was done manually via e-mail, paper records, and visits down the hall. Five or six years ago, one of Myers' engineers put together a system for tracking order fulfillment called MOTS (Myers Order Tracking System), using Allaire's Cold Fusion and a Microsoft SQL Server database that allowed Sales and Account Management to enter orders, which were then fulfilled by the Support, Engineering, Design, Information Systems, and Accounting departments. This system, although an important step forward, still left a lot of manual steps and was not integrated with any other business system.

Around the same time, a system was created in which clients and sales representatives could order Myers Web site products online. This system could create new Web sites and total the setup and recurring charges for the Web site packages offered. It then sent e-mail to individuals, who could enter orders into MOTS and create billing information in the accounting system.

Architectural Speed Bumps

Several systemic problems plague this kind of architecture. At Myers, one of the more obvious included the manual data entry required to initiate order tracking and the errors created as a result of this manual process. Another was the disconnect between the order entry, order tracking, and billing systems in the company and the dropped orders, missed information, and errors that resulted.

Another problem that manifested itself only on occasion was that the MOTS system itself had inherent gaps. Because of how MOTS was written, orders could be entered that had no departmental assignment or had lost their assignment. When this happened, the orders ended up getting lost in the system. When orders became lost, accurate, timely billing was harder to achieve.

As the business grew, the gaps in the architecture became increasingly obvious, and as the number of clients and orders increased, the frequency of dropped and miskeyed orders caused measurable revenue impact. In addition, the quantity of manually entered data caused delays and process inefficiencies.

Due to the increasing revenue impact and the decreasing efficiency within the fulfillment organization, it became clear that a replacement system was necessary to tie everything together, improve efficiency, and decrease error rates. The old system is diagrammed below.

The diagram shows all the areas in which manual data entry was required. Because none of these systems were integrated, there were many opportunities for data to become lost or distorted. The global requirements became immediately obvious.

• The order system needed to tie directly to the fulfillment tracking system.
  
• The system needed safeguards to keep orders from falling out of the system without being processed.
  
• Accuracy needed to be maintained to ensure exact billing and correct fulfillment of orders.
  
• The system needed to minimize internal costs. To that end, the system needed to be created quickly but with complete functionality.
  

Although a good order entry and tracking system can help keep costs down, it does not generate revenue in and of itself.

Getting Down to the Bone

Before the schema design was started, some fundamental architectural issues needed to be resolved. The first underlying technical requirement was for the system to be configurable without requiring additional coding. In essence, this meant that the workflow needed to be embedded within the database rather than hard-coded in the rendering/processing code. Second, the database needed to contain enough information to be able to render key (and changeable) aspects of the interface for order entry as well as fulfillment processing.

In the effort to address the above issues, the system lent itself to two components—order entry and order tracking—and well-defined glue between the two. The order entry system needed to know how to render order forms with accurate product codes, discounts, and pricing terms. The order fulfillment system needed to know how to track the various types of tasks, related jobs, and individuals to process and bill each order. Finally, the conversion of orders into fulfillment jobs needed to be regular and predictable. The following diagram represents the structure of the new system as it exists today.

This diagram shows that all information roads lead to the new order system, which sits on the back-office portal administration site. All of the initial data entry is done just once, with each team required only to validate the data at each stage of the process. With the introduction of automated data transfer from the order system to the accounting system, another key area of critical data transfer was automated.

Counting on PHP

On a purely technical level, it was decided early on to use PHP as the primary development language and Oracle as the data repository for the system, for several key reasons. First, Myers' existing back-office portal was written almost entirely in PHP against an existing Oracle database, removing one potential source of incompatibility. It also meant that to create this new system, Myers could leverage the in-house talent that had produced the existing back-office portal.

Second, empirical testing revealed that PHP provided a relatively high performance level, compared to other development languages. Because PHP is resident within the Apache server as a dynamically loaded library, there is no additional startup time for each connection to the system. Moreover, improvements in PHP optimization (through the Zend project) meant that the typical operations performed within the code were not measurably slower. Finally, the OCI interface module written for PHP is compiled and optimized in C code, making it very efficient to access the Oracle database.

Third, we learned that because PHP code embeds itself within the context of HTML, it becomes more natural for designers and programmers to create cooperative user interface functional code. Although this last characteristic is true for other server-side scripting languages as well, Myers found PHP to be less likely to create conflict between developers and designers. In addition, the syntax for PHP and the available code library meant that it could do everything it needed to do.

Finally, another benefit of having all the code embedded within HTML code is that source code control requires only placing standard text files under revision control. We use CVS as its standard revision control system. Because PHP code does not have to be compiled in any way, creating a "build" of the system involves only retrieving the text source files from the repository and placing them on a Web server. This meant that we could publish incremental bug fixes for its test and production environments by using control mechanisms in CVS, without needing to create complicated build systems.

Designing Schemas to Support Reconfigurability

The basic schema diagram below shows how the order system was constructed. Both of the major schemas were divided into prototyping and transactional tables. The prototyping tables allowed the system to be reconfigured without being recoded whenever business situations change. The transactional tables contained the order and job details for the actual client orders.

These schema diagrams look complicated, and, of course, they are. However, if they are divided up so that only the prototyping tables (the ones ending in _def) appear, the fundamental structure of the architecture becomes clear. Orders are composed of line groups, which contain details, order lines, or both. Order lines can optionally create jobs, which consist of a sequence of tasks and contain several details. These details are required to be entered for various tasks. Tasks appear in distinct queues that are accessible by particular users in various departments.

To prove the system, the strategy was to prototype the order system in stages. The first part of the system to be proved was its ability to create an intelligible order form from the order prototype tables alone. Once the initial schema definition was completed, the order form generator was the first visible part of the system prototyped.

The team assembled to build out and configure this system included three developers in addition to the managers of the various departments most affected by the system. The developers were divided between working on configuration functions, display functions, and transactional functions. The department managers provided valuable feedback throughout the initial build cycle on the types of interfaces that would enable users to enter and process data.

Rendering the User Interface with PHP

The initial order form to be prototyped was the basic Web site order form, available at webwiz.myersinternet.com/. The resulting order form was created in PHP by a single developer in a period of three days. If the order prototype definition, with only a layer of PHP code between the database and the browser, was to be able to completely define the look and behavior of order entry, a level of compromise was required in the database design. To this end, constructs such as the order line groups had to serve two purposes: (1) provide visual separation on the input form, so groups of like items could be rendered together, and (2) functionally group like items, such as items to which a percentage discount applies or a list of options from which to make a unique choice.

Because PHP was the development language, the prototype came together quickly enough that required changes to the schema could be made and the form generator could be recoded quickly and in tandem. In addition, because the schema was designed with the rendered user interface in mind, schema changes and adaptations were easy to make as new visual requirements emerged during the prototyping process. The resulting generated form looked like this:

Creating a Fully Functional System

After rendering of the order form, it needed to be made fully functional. First, the system needed to save transactional order data entered on the order form. Second, people filling out the order form needed to be able to fill it out based on in-progress order data. Third, for orders requiring payment at the time of submission, a payment process was necessary. Finally, submitted orders needed to create the requisite fulfillment jobs and their task dependency.

The quick turnaround times that PHP development affords allowed for the complete order entry part of the system to be fully prototyped, both visually and functionally, within a two-week period. This allowed for quick feedback and reaction to the system architecture and its realization. The fast turnaround meant that issues were addressed while the system was still fresh in everyone's mind. In addition, because there was no extra layer of coding between the UI and the back end (such as a middle-tier server), the process of creating the prototype required fewer development steps and fewer developers.

Once jobs could be created transactionally as a result of an order, the various aspects of the order fulfillment cycle could be created. The job fulfillment process centers primarily on two UI screens: task assignment and task processing. The task assignment page consists of viewing unassigned tasks in queues to which a user has access as well as showing tasks assigned to the user that need to be processed.

One part of the development cycle that was key to making the whole system consistent was the search for common functions that could be abstracted into a code library. There were several cases in which particular subfunctions and data points needed to be used on multiple pages. Whenever developers found themselves doing essentially the same series of steps on one interface page that they had done on a previous interface page, it was appropriate to evaluate the benefits of abstracting this function out into a PHP code library.

The task processing page needed to show the relevant details from the order as well as the details for the job, both those completed by previous users and those that had to be completed by the current user. This page came together quickly as an initial prototype but needed to go through several evolutions before it was truly useful for those who would be employing it daily.

The most complex operation on the task processing page arose in connection with realizing all the database steps for closing a task:

1. When a task closes, mandatory tasks that depend on the current task need to be open.
   
2. Moreover, when a task closes and it is the last open required task for the job, the job needs to close.
   
3. Finally, if all the jobs for a particular order are closed, the order needs to close.
   

Although this logic could have been placed in the PHP code, because Perl scripts were going to be used for some automated task processing, it was decided to do these operations in a common format. For the task-closing procedure, all the operations were based solely in the database, so it was logical to write this procedure in PL/SQL. This allowed any programming language that needed to close tasks to call a consistent procedure to do all the database operations required.

The automated task system was created with the automated task definition table as the configuration and the automated task run table as the transactional table for individual automated tasks. Because the automated tasks needed to do things such as call previously written CGI scripts, we was decided that Perl could accomplish all the technical details required. The vast library of readily available Perl modules allowed for various kinds of operations that provided all the flexibility required. The system itself consists of a server process that stays resident on a system that wakes up every minute, looking for pending automated tasks. If any pending automated tasks are found, they are executed with the relevant task details and automated task configuration parameters. Both success and failure of automated tasks are logged into the database. When automated tasks succeed, calling the requisite PL/SQL procedures closes the tasks.

Stitching the Prototypes Together

Once the system had functional prototypes for all the major parts, it was ready to be configured with all the order types required as well as all the current business fulfillment cycles. The order prototypes consisted of new-client orders, existing-client orders, and special orders. The new-client orders typically involved sales representatives entering new-client details and usually required prepayment of setup fees. The existing-client orders ran the gamut from Web site upgrades to full and partial cancellations, to service and billing changes. The special orders generally involved items orderable only by relatively senior personnel.

As the configurations were created and the system started to take shape, the quick-development-time advantages of PHP again proved their worth. When it emerged that the task-processing page needed to show the details for not only the current job but also for all the other jobs that were part of the same order, that capability was added after just a few days of development and testing. When a new automated task was needed that would keep a job in Sleep mode until a particular date or until a certain number of days had passed, the development, testing, and deployment were completed in just a week.

Another system advantage that became apparent after the system configuration started was that the task dependency configuration clearly showed current business processes—especially those that crossed departments—in a way that was easy to see and adjust. A combination of the database dependency information and a package called "dot" from the AT&T Research Graphviz package (see www.graphviz.org) meant that fulfillment cycles could be displayed graphically, based on live configuration data. The following graph is a sample image of the job fulfillment cycles required to set up a Web site package.

In addition to the transactional pages, the various metrics the system collects allow for much business process reporting. Revenue reports based on products ordered as well as fulfillment reports on order turnaround can be created and modified as business requirements arise and change. These reports can be created in PHP with standard HTML output or written in Perl to create Excel spreadsheets as required. Creating summative views with Oracle's analytic and grouping functions allows for consistency of reporting as well as an easy way for DBAs to perform optimization.

Project Timeline

Development of the system began in May 2003. By June 2003, the major system components were all completed and the final and complete configuration of the system began. By late July 2003, the system was completely configured and internal training began. The system has been in production mode since August 2003 and has processed more than 4,000 orders for nearly 2,000 clients and more than 6,000 fulfillment jobs with nearly 20,000 completed tasks. Since the system went live, users have been able to create new order and job prototypes without intervention by developers. In addition, the system is now being configured to serve as a client trouble-ticket system with only new configuration and no new coding required.

What made this a successful system was the combination of a flexible architecture and fast development turnaround. Although this system could have been created in several different ways, the choices we made meant that it could be created quickly with a great deal of flexibility and a very low development cost.

The following table shows the development times, in developer man-hours required to create each of the components and push them through into a production setting. In addition, it includes an indication of how long development took to create the system that the new order system replaced, where applicable.

Component	New System Development Time	Old System
Web site sales order form	32 developer hours 7 business configuration hours	200 developer hours
Upgrade order forms	35 business configuration hours	NA
Job assignment forms	12 developer hours	40 developer hours
Job processing forms	72 developer hours 15 business configuration hours	240 developer hours
Job conversation log forms	12 developer hours	NA
Hourly billing tracking forms	8 developer hours	24 developer hours
Productivity reports	48 developer hours	NA
Commission reports	6 developer hours	NA
Revenue reports	40 developer hours	NA
Marketing reports	24 developer hours	NA

Lessons Learned

The key lessons learned from the architecture and implementation of this system were the importance of quick turnaround in creating functional prototypes, creating tight integration between UI configuration and UI code, and keeping as much of the system as possible configurable instead of coded. Between the simplicity of the Oracle/PHP development model and flexible prototype configurations in the system, it clearly demonstrated the shortest distance in time and materials between initial concept, functional prototype, and production system.