Chapter 4

Creating Design Specifications


The pace and economics of development projects today do not allow for the thick specification documents of days gone by. In this chapter, I'll describe how to survive the specification process and to produce a useful control document for application development. I will show:

Staring at Blank Pages

"During the development of Access 1.0, we spent a lot of time defining the structure and format of all of our specifications and internal design documents. In fact, one could argue that we spent an inordinate amount of time. The very first printing of the Cirrus (Access 1.0) design document was over 200 pages long, with all the proper sections and headings, yet every page simply had the words 'Text to Come' on it."

Tod Nielsen, General Manager, Microsoft Access Business Unit


When I was in high school and life was simpler, my friend Dave and I would jump into my car each day after school and drive. It didn't matter where we went, we just liked to drive. While this is a nice way to pass an afternoon, I would not recommend it as a metaphor for application development. Some developers sit down at the computer without any greater sense of direction than Dave and I had. They don't know where they're going either; they just like to code.

To prevent unstructured wandering, whether on the road or the keyboard, we use maps. The map of an application (see Fig. 4.1) is called a design specification (spec), and a well-written spec saves money and time for developers and their employers or clients.

Fig. 4.1

A specification is the map of an application, providing the boundaries and also the direction of travel.

One common perception is that a spec provides the development boundaries for programmers who are apt to wander unless given a map denoting where to go, how to get there, and by when. This notion was true back when coders were salaried and could measure delivery times in years. In those days, a thick spec ensured the following:

The modern paradigm is different-we now work in a world where many coders are consultants, contractors, or staff developers who work for-or at least are accountable to-the users. And development times are now measured in person hours, not months or years. In the current scenario, the spec still protects the users and helps ensure that they get what they asked for. In addition, a spec also protects the developers, but no longer by providing job security. Instead, developers should now use specs to do the following:

On the highest plane, a spec is the bridge between the disparate worlds of user and developer and provides a common reference point and communication tool for both (see the sidebar). In my company, we simply won't do a large development project without a defined spec. Any client who won't commit to a good design phase is dooming us to fail, so why start?

Each But Not Both

One common type of miscommunication in the solution design process arises when users speak in non-technical terms but developers hear in technical terms. For example, when users try to describe to developers the criteria requirements for a query operation driving a data sheet, data export, form, or report, the two sides don't always apply the same meaning to the terms "and" and "or."

Consider the following predicament (derived from one of our application development projects). The users need a contact management system with parent contact person records (with fields like ContactID, ContactName, and so forth) pointing to multiple child records for contact events (ContactID, EventDate, EventType, and the like). Assume that Mr. Gill Bates is in the contact table and that there are two contact event child records for Gill: one with an EventType of "Letter" and one for "Call."

Next, assume the users tell you that they want their new contact management system to produce a report showing "any prospect that has received a letter or a call." As a developer, you will focus on the "or" in their request and mentally picture an OR operator in an SQL WHERE clause, with the SQL selecting the distinct ContactID values from the parent table where the child records match EventType = 'Letter' OR EventType = 'Call'. This query will return all parent records that have child records of type letter, call, or both.

What the users actually envisioned from their request was that the report would show all contact persons who received either a letter or a call, but not a letter in addition to a call (as is the case with Gill Bates). In their vocabulary, the term "or" means "one or the other but not both," because if they wanted both they would have asked for "a letter and a call." In contast, you and other developers thinking in terms of the SQL language will envision "one or the other or both" to solve their request when you hear them say "or."


The point here is that without a spec document, the developer creating the report is unlikely to clearly understand an issue like this. Equally important, though, is the fact that a written spec does not by itself solve this communication problem. Only a specification that is detailed enough to define the query behind the report, and to show sample data for the report, will provide users with the opportunity to catch the developers' misinterpretation before coding begins.

With the days of largesse in software development efforts gone (if you've been around for awhile you may recall the era of million-dollar mainframe software projects), the tight budgets of the 1990s often require a software project to be built like a cheap movie: shot in one take (see Figure 4.2). This leaves little margin for error in the development cycle and requires a good project specification "script" to work from to ensure success.

Fig. 4.2

If an application was a feature film, the specification would be the script.

When I ask a new client or another solution developer why they don't do good specifications for their projects, the most common answer I get is a simple one: "We don't have a good formula for doing specs." They fear the 300-page document syndrome or some unreadable complex technical tome and so they simply shelve the idea. My goal for this chapter is to provide that "good formula" that will make it easy for you to decide how you want to configure and write specifications.

Preventing Development Problems with a Specification

There is basically no such thing as a specification that is too detailed-some of our larger clients have produced more than 300 page specs for us to work from. The majority of Access applications do not have the complexity or budget to warrant such monolithic documents, however. The key objective is to have the specification-and the research process that creates it-clarify users' goals to developers and describe the tools and techniques to achieve the goals. Quality, not quantity, is the objective, and the hallmarks of a quality design document are comprehensiveness and clarity.

In an application development project, both the users and developers are at risk. The users stand to lose money, productivity, and perhaps even customers if the project is not successful. An in-house developer stands to lose his or her reputation or perhaps even job, while contract developers may lose an important client, and often actual cash, when a project goes awry. All of these parties are better protected by a good design document.

The degree of protection the spec document provides lies in the balance struck between user interests and developer interests. As an example, our specifications have always included milestone dates related to various phases of the project (recall the application phases such as prototyping from Chapter 3, "Preparing for Development"). These dates were originally intended to protect our clients by providing them with a written obligation that we would deliver portions of the project on specified dates.

Over time, however, we expanded this model to include the subsidiary milestones within each phase that require client involvement, such as reviewing the prototype or providing testing effort. We added these dates to protect ourselves because documented milestones require clients to commit to delivering their portion of the project on specified dates. This enables us to schedule our time better and to hold them accountable if they negatively affect the project timeline.

Generally, the benefits derived from a good spec fall into the following three areas:

Saving Time with Specifications

In the old frontier of the American West, gunmen were said to have "itchy trigger fingers" if they were too quick to draw their pistol. In the new frontier of technology, too many programmers have "itchy coding fingers" and are too quick to begin coding a project without defining it. Nothing is more frustrating to the itchy fingered developer than to plod laboriously through spec development because this takes time away from the joy of coding. However, the time spent on design work seems always to save an equal or greater amount of time on recoding and reworking features of a project, thus justifying the cost for the effort.


Successful developers understand and support the spec process because it protects them from the least enjoyable type of development work: rework.

Project time savings accrue from a specification in the following areas:


For added leverage, have the design team members sign or initial the final specification document. You have a much stronger position from which to resist enhancement requests when you can point to the signature of the requester and say, "But you agreed in writing that this was the final design."

Remember that, even in spite of your best efforts to control feature expansion, you may lose the battle in an environment where "the customer is always right" is the prevailing maxim. Your only insurance policy when feature expansion occurs is a time and money allocation in the specification document for contingencies. See the section "Estimating Project Costs" later in this chapter for more information.

In a perfect world, each spec is comprehensive and limiting enough that it need not, and cannot, be changed once development begins. In the real world, change is inevitable, but having a spec at least helps to clearly identify the deviation of the change from the original intent.

Saving Money with Specifications

It seems obvious to say that a good design specification will save money because the application can be built in one pass instead of multiple iterations. And yet the importance of this point cannot be overstated. An equally important point lurks within it: A good specification will help determine whether the project should be done at all.

I've seen projects that survive the research phase, are written up into a brief specification, and then are developed. Further into the development process, issues that were not properly clarified in the specification crop up and begin to slide the time frame and cost of the project outward. In a project with a very tight budget, any such perturbation may be enough to "break the bank"-in other words, to make it apparent to management that the project cost is going to exceed its budget and trigger an immediate cancellation of the project. A substantial amount of capital and human energy is thrown out when this happens. All parties would have been better served by an accurate spec that showed the true effort, budget, and intent that the project should not be initiated with the presently available financial resources.

The cost of creating a spec is not always trivial. The litmus test for the success of the effort is whether management feels that the investment was recouped by cost savings elsewhere in the project. Such cost savings may accrue in several areas, such as in the following:

Empirically measuring the economic value of a spec is difficult because the cost of the problems it prevents cannot be calculated if the problems never occur. It takes foresight on the part of project management and the design team to commit to the process and believe in it despite their inability to measure its worth precisely.

Enhancing Communication with Specifications

The process of researching, creating, and reviewing the specification forces the design team to discuss features and functionality at a very detailed level. Subtle features that may be taken for granted-for example, the location and wording of form command buttons-will end up on the agenda for discussion and definition.

When nothing is left to chance, the possibility for acrimony and contention between the developers and users is mostly removed. However, project timing and budgets rarely allow for the rich level of detail that can prevent all situations leading to communication gaps (see the sidebar). Throughout the spec process, you will have to make decisions about the appropriate level of detail in each section and for each feature. In general, of course, more detailed specifications enhance communication among project team members.

Do You Speak My Language?


Simply having a specification is not always enough to aid your success. The specification must also be written in a manner that is not ambiguous and uses language that all parties can understand. Also, members of the design team who are aware of the communication challenges in application development must review the specification. The following example provides a good illustration.

On a recent client project, time pressures allowed us only a brief, but precise (or so we thought), specification. The application would track employee information, and each record included the following fields:

We were told that each employee had a supervisor, and each supervisor a manager. For convenience, the client wanted us to store the manager's name in the table even though it could have been joined in from a lookup on the supervisor name.

From this design, we inferred that the terms "supervisor" and "manager" when used by the client were literal references to the fields in the system of the same name. Thus, everywhere in the system where the client told us to display or report the employee's manager, we used the field called ManagerName. The client approved the screens during the prototype phase, but during testing, they complained that the manager information was not correct on reports.

It turned out that in their vocabulary, even though an employee record contained a manager's name, only supervisors truly had a manager. For employees, their supervisor served as their manager. Thus, every place in the system where we had displayed or printed ManagerName needed to be changed to show SupervisorName if the employee involved was not a supervisor. The extra time, confusion, expense, and ill will that this miscommunication generated was tremendous, and the project was delivered late as a result.


In reviewing the process to determine how we could have avoided the situation, it turned out that what we put in the specification would have had to be incredibly detailed to trap this problem because the client's misuse of his own terminology would have confused all but the most thorough review process. The solution in this case may have been to lay out the forms and reports on the specification, displaying actual employee data records for the company. Team members at that point possibly might have noted that we were putting the manager's name where they expected to see the supervisor's name in some cases.

As you prepare your specifications, be alert to such potential communication pitfalls.

Communication gaps occur most often when you work with forms and reports and less often in application structures and processes for several reasons:

Peeling the Onion

"Too often, our users tell us what they want an application to do, but they are unable to put into words how they want the screen forms to match the workflow. If we attempt to document (in a spec) what we think the users want to help them review the spec from a usability standpoint, then additional requirements and clarifications often come to light. We call this process 'peeling off the next layer of the onion.'

"Poor screen navigation and awkward usability will cause an application to be unsuccessful faster than you can say, 'I should have done a spec.' I have seen applications with only a marginal data structure design become quite successful in spite of themselves, simply because they were easy to use. Placing menu and screen navigation designs in the project spec enables the users to compare the developers' interpretation of their needs with their actual business needs."

David M. Crosby, Senior Applications Consultant, Washington Mutual Bank


Some kind of layout for form and report objects-even hand-drawn-will improve the communication of features through the spec. This point is discussed further in the section "Helpful Tools for the Design Process" later in this chapter.

Specs also enhance communication between the management members of the design team. A spec that details project timelines and costs provides a powerful management tool for budgeting cash outflows, scheduling personnel resources for each project phase, estimating overall life cycle costing, and creating delivery schedules. The program manager for the project rarely keeps in touch with the day-to-day machinations of the application's development. Instead, he or she will often select milestones from the project spec and periodically review the development progress against those milestones with the project manager.

For the project manager, the spec provides the major benchmarks that define the success or failure of a project. With the spec as the ruler, the manager can quickly determine at deployment whether the feature set was delivered and whether the timing and cost targets were met.

Pulling the Research Together

A spec based on faulty or limited research is no better than a map made without walking its territory. Before you create the specification, you must complete your research using the guidelines described in Chapter 3, "Preparing for Development." The strategies presented in that chapter were intended to convince you to be thorough in your research practices. Remember that if you cut corners on the research, the spec will be less accurate as a result.

The outputs from the research phase of the project will be threefold, as follows:

The first step in creating a specification from the research data is to clarify and prioritize the requested feature set. While features may be cut later in the specification process as cost estimates are attached to them, some feature requests that are simply not worth even putting in the spec may stand out after the research effort. For example, a feature that obviously does not fit cleanly with the other features and will create a large coding and integration burden with respect to the expected benefit may be killed or deferred before it ever gets onto the spec. Try to determine at this stage which of the requested features are actually required features.

The second step is to mix the features into a cohesive model. Like puzzle pieces, all the user requirements must be laid out and fit together into an application design that provides the needed benefits while employing the simplest possible interface at the lowest cost.

The third step is to define a plan for implementing the development (see Figure 4.3). Human resource requirements, computing infrastructure needs, time lines and milestones, tools and techniques to use, and responsible parties must all be determined.

Fig. 4.3

Pulling the research data into a specification follows this flow.

The specification process can be lengthy for large projects, but for any project, the probability of success of the development effort is directly proportional to the amount of time spent creating a detailed specification for that effort. With peoples' job performance often tied to a mission-critical software application, both users and developers have much to lose from a sloppy specification process.

Elements of a Specification Document

The needs of the project, your development style, design team mechanics, and similar factors will affect the organization of information that you place in each design specification. There is no particular reason to be rigid in your formatting or to have each specification match the layout of a previous one.

I believe that there is a minimum set of information that each specification should include or at least that the design team should consider for inclusion. This minimum set of specification sections includes:

  1. Executive Summary
  2. Application Processes
  3. Project Mechanics and Management
  4. Data Structures and Rules
  5. Screen Forms
  6. Reports
  7. Appendices

The flow of these document sections reflects our standard approach, which puts the three sections that managers and executives are most likely to read first and the technical information after them. In your specs, I recommend that the Executive Summary remains the first section, as is standard practice for business documents. The placement of the other sections is not fixed, however, and you can vary the order to suit a particular project.

The spec outline that follows provides a top-level view of the organization and content of my suggested specification layout. I provide more detailed descriptions of each topic in the spec document example in Appendix B, "Design Specifications-A Detailed Outline." You should read that appendix or its related file SPECSHEL.DOC on the CD-ROM before using the following outline for your own specifications.

Topics in the "Executive Summary" Section

An Executive Summary provides a clear, accurate, and concise synopsis of the project by highlighting the project's functionality, justification, and cost.

  1. 1A. Overview
  2. Identified Problems
  3. Proposed Solution
  4. Project Scope
  5. lB. Justification
  6. Cost Justification
  7. Return on Investment
  8. 1C. Resource Requirements
  9. Human Resources
  10. Physical Resources
  11. Capital Resources

Topics in the "Application Processes" Section

This section describes the solution from a functional standpoint, including the software's primary tasks and the processing algorithms for the tasks.

  1. 2A. Solution Description
  2. 2B. Primary Processes
  3. 2C. Application Navigation
  4. Launching the Application
  5. Interface Philosophy
  6. Navigation Map
  7. 2D. Initial Data Conversion
  8. Source of Initial Data
  9. Converting and Validating Initial Data
  10. 2E. Links to Other Systems
  11. Downloads
  12. Uploads
  13. Merges and Links
  14. 2F. Security Requirements
  15. Workgroup Security
  16. Application Security
  17. 2G. Multi-user Issues

Topics in the "Project Mechanics and Management" Section

This section describes how the project will be managed and delivered.

  1. 3A. Project Management Overview
  2. 3B. Architecture and Tools
  3. Platform
  4. Development Tools
  5. Reusable Components
  6. 3C. Equipment Requirements
  7. Client Configuration
  8. Server Requirements
  9. Connectivity
  10. Equipment Upgrades
  11. 3D. Application Deployment
  12. User Definition
  13. Review Builds
  14. Testing
  15. Unit Testing
  16. System Testing
  17. Test Plan
  18. Preloading Data
  19. Training
  20. Installation
  21. Online Documentation
  22. User Documentation
  23. System Documentation
  24. Database Administration
  25. Administrators
  26. Backup Policies
  27. Disaster Recovery
  28. Adding Users
  29. Localization
  30. Ongoing Support
  31. Supporting Users
  32. Reporting Problems and Enhancement Requests
  33. Problem/Enhancement Resolution Guidelines
  34. Future Releases
  35. 3E. Project Management
  36. Affected Users and Related Parties
  37. Project Timelines
  38. Responsible Parties
  39. Project Administration
  40. Issue Management
  41. Risk Management
  42. Coding and Documentation Standards
  43. Spillover
  44. Future Phases
  45. 3F. Financial Mechanics
  46. Costing
  47. Third Parties
  48. Contractual Issues

Topics in the "Data Structures and Rules" Section

This section provides the blueprint for the database structure by describing the data tables and business rules.

  1. 4A. Data Models and Diagrams
  2. 4B. Schema Definition
  3. Table Structures
  4. Stored Views
  5. Table Relationships
  6. Business Rules
  7. 4C. Data Load
  8. Initial Volumes
  9. Future Volumes

Topics in the "Screen Forms" Section

This section provides the form designs and describes the standards and guidelines that affect form design.

  1. 5A. UI Guidelines
  2. Form Layouts
  3. System Messages
  4. Fonts and Point Sizes
  5. Colors
  6. Buttons
  7. Switchboard Menus
  8. Bar Menus
  9. Shortcut Menus
  10. Toolbars
  11. Keyboard and Mouse
  12. Status
  13. Terminology
  14. 5B. Structural Information
  15. (name of the first form)
  16. Purpose
  17. Mockup
  18. Data Source
  19. Navigation
  20. Controls
  21. Events and Procedures
  22. Properties
  23. Validation
  24. (name of the second form)
  25. (repeat the topics above)

Topics in the "Reports" Section

This section provides the report designs and describes the standards and guidelines that affect report design.

  1. 6A. UI Guidelines
  2. Report Layouts
  3. Fonts and Point Sizes
  4. Colors
  5. Bar Menus
  6. Shortcut Menus
  7. Toolbars
  8. Terminology
  9. 6B. Structural Information
  10. (name of the first report)
  11. Purpose
  12. Mockup
  13. Data Source
  14. Navigation
  15. Controls
  16. Events and Procedures
  17. Properties
  18. (name of the second report)
  19. (repeat the topics above)

Topics in the "Appendix"

Budgeting from a Specification

For prospective users, the design process's primary challenge is to define and clarify the short- and long-term feature sets for the application. Philosophically, the developer's primary design challenge is the same; but in practical terms, the developer's primary challenge is actually to properly estimate the time and cost budgets for the project-in other words, to tell the users whether they can afford what they have requested and when they will have it.

Notice that I mentioned time and cost budgets above, as if they are equally important. In fact they are, but not to the same audience. At the completion of the design, users are now very excited about their new application and would like to have it completed as soon as possible. Thus, their main focus is on the time budget-the delivery milestones for the application.

Conversely, managers are primarily concerned with monetary budgets, and are usually content to delay the completion of the project if the cost can be reduced, either through using less-expensive development resources or using some existing company resources. Thus, for the developer, the budgeting process involves finding a balance between the speedy delivery of the application and the most cost-effective delivery methods.

Estimating Project Costs

The cost of a project is equal to the number of hours of work multiplied by the labor cost per hour. Right? True, yet with such a simple formula at its heart, the project costing process is frequently inaccurate and developers who do similar work day after day still seem unable to accurately cost their work. When a project is not completed on budget, the blame is placed on the developers for either working too slowly or poorly estimating the scope of work. Several other factors may actually be to blame, however, such as the following:

Once these two factors are eliminated-the first by good design and the second by contingency budgeting-then the responsibility for meeting a project budget again lies with the development team, and accurately so. Therefore, when you apply development time estimates to a specification, your numbers had better be accurate, or you will paint yourself into a corner.

If you create Access applications on an ongoing basis, your ability to estimate project costs should be high. The knowledge you gain from developing each application should carry forward to the estimates for the next application. If your estimates of future project time requirements are to be based on historical development times, you must keep good project logs and records while you develop each application. The information in your records will eliminate any guesswork when you compute project times for new projects.

For example, you may find that with your combination of development style and tools, you can produce the average Access form for an application in two hours. As you budget a new project, you can list each form in the specification then apply the two hours per form to start the time budgeting process. You would then modify the budget based on the deviation of each specific form's complexity from that of the average two-hour form and account for any special needs of the application, such as enhanced validation or processing.

It is very simple to keep good project time information. In my company, we log time in an Access database against individual projects and their components (see Figure 4.4). At the end of each project, we can query the database and determine how time was spent by project, by developer, and by feature. This information helps us to determine where time was misspent on a particular project but also helps us to develop rules of thumb for budgeting projects in general. We apply these rules of thumb to application budgets as our starting point then adjust them based on the specific project requirements. (Project time analysis is also discussed in Chapter 9, "Completing and Deploying a Solution." That chapter also describes the SpecItem column shown in the figure.)

Fig. 4.4

Database records in Access can be used to log and analyze project time.

Table 4.1 shows the rules of thumb that we use to devise the time requirements for various deliverables in an Access application. These numbers represent our factors for the standard flavor of an object in an average application; we use these factors as a starting point for budgeting in our specification documents. You may use them for informational purposes or perhaps for budgeting your first Access project, after which time you will develop your own benchmarks based on your own user base, techniques, and experiences.

Note that the table data is based on our approach to Access expert solutions development as reflected in this book and may not be indicative of your approach or that desired by your users. Also, some actual object development times will vary far from these averages due to their feature set in a specific project. For example, forms for lookup tables are usually very simple and take less time than the average shown in the table, while complex forms with background processes and user interface tricks may take several times the average hours. The table merely provides numbers to use when starting the budgeting process.

Table 4.1 Development Time Guidelines That We Use for Access Application Objects

Object Type

Budget Hours

One data table (1)

0.5

One form switchboard menu (2)

2.0

One record list form (3)

3.0

One data entry/edit form

3.0

One summary report

1.0

One detail report

1.5

One lookup table form

1.0

(1) A data table includes the overhead of schema items related to the table, such as properties, indexes, relationships, and standard (default) queries.

(2) Clients usually request that switchboard menus include an application- or company-specific graphic image that we create or clean up.

(3) The record list form is a Get form we commonly create so that users can list, sort, and find records. See Chapter 16, "Presenting Data to Users," for an example of this type of form.

How did I arrive at the numbers in Table 4.1? In our early days of Access development, we created a set of target times for object development. Over the years, in project postmortem meetings, we have reviewed our time estimates and adjusted them to reflect our experiences up to and including that project.

To further legitimize the information in Table 4.1, I was curious to see if our own statistics would defend our rules of thumb, so for this chapter, I reviewed time log information for eleven of my company's recent Access projects. The results are summarized in Table 4.2, which shows the averages of the actual time expended per object when developers created the first release of the eleven selected projects (note that our time log data did not support statistics for every object type listed in Table 4.1). I selected average small projects that were not overly complex, so your actual development time for objects may vary wildly in either direction from these numbers.

Table 4.2 Average Development Time per Object for Eleven Leszynski Company, Inc. Access Applications

Object Type

Average Hours

One data table (1)

0.2

One form switchboard menu (2)

3.0

One record list form (3)

3.5

One data entry/edit form

4.5

One report

1.5

1) A data table includes the overhead of schema items related to the table, such as properties, indexes, relationships, and standard (default) queries.

(2) Clients usually request that switchboard menus include an application- or company-specific graphic image that we create or clean up.

(3) The record list form is a Get form we commonly create so that users can list, sort, and find records. See Chapter 16, "Presenting Data to Users," for an example of this type of form.

What my calculations in Table 4.2 show is that for several object types, the actual time expended was greater than the average budgeted time we applied to our specs from Table 4.1. This is a tangible manifestation of feature creep-our estimates were less than the actual time requirements because users changed the design of the interface during development in almost every project reviewed. As developers, we budget a project based on the time it should take to do the work, but change requests or technical problems can cause the actual time to exceed our estimate.

If the budgeted time and actual time consumed by object development for our reviewed projects were not equivalent, you're probably wondering where the money came from to pay the difference. In the analyzed cases, and in most others in my company's experience, the money for a budget shortfall or change of scope comes from one of these sources:


When you create a project budget, add up all the time items and then apply a contingency factor to the total budget to allow some extra money for minimal design changes and unforeseen problems. Depending on the nature of the project, we typically include a Contingency line item on the budget that equals between five and 20 percent of the total project budget.

Table 4.2 also provides an additional interesting statistic. Notice that the time it takes us to create tables and the database schema (0.2 hours) is dramatically less than the amount of time we budget (0.5 hours). This results from the fact that our budget times still reflect the assumption that tables and indexes are built manually, when in fact we generally use our LCI Schema Builder to complete the process much faster. This tool is described in Chapter 10, "Creating Expert Tables and Queries," and provides a dramatic example of the economic benefit of development add-ins.

Table 4.3 gives statistical information for the time spent on each project component for the same eleven projects in Table 4.2. The second column in the table shows the overall average actual total time we expended on each project element. The third column shows the overall average percentage of a project's time that we spent on each element. You can use these percentages as guidelines to help you begin your own spec budgeting process.

For the data in Table 4.3, the average total project time was 203 hours for the eleven projects, the smallest project reviewed was 103 hours, and the largest project was 398 hours. (The totals in the second column add to 225 hours as opposed to the average project size of 203 due to statistical mechanics.)

Table 4.3 Average Development Time per Project for Elements of Eleven Leszynski Company, Inc. Access Applications

Project Feature

Average Hours

Average Percent

Design, specification, management

56

28

Supporting UI code (1)

19

9

Schema development

6

3

Forms development

49

21

Reports development

19

9

Processing code (2)

35

12

Testing, deployment, training (3)

41

18

(1) Supporting UI code is the user interface and library code that is not related to a specific form, such as navigation, generic validation, error management, and login.

(2) Processing code includes routines that are specific to the application's purpose, such as accounting processes or upload/download routines.

(3) Integration/beta testing time is reflected in this item, but time for unit testing of each application object is buried in the object development times. Remember also that our business model offloads much of the testing to users; in environments without access to users as testing resources, the percentage of project resources spent on testing may be much larger.

Bear in mind that a single time record on our time logs may sometimes include work on several objects, so the numbers shown in the previous three tables may be mildly skewed due to members of my staff lumping several work items into one time log entry. Also note that the nature and complexity of projects that come to us as a firm may have no correlation to the type of projects that you do-some of the things we do with Access are rather complex. Despite the caveats, the information above should help you to understand the time burden of Access development.

Thanks to Microsoft's successful advertising about the ease of using Access, your clients and users will think that you can generate an Access application in a few dozen hours. While you may be able to create a simple application in thirty to a hundred hours, creating an expert solution takes much more time. Our standard answer when a new client calls and asks, "How long does it take to create an Access application?" is "The average high-quality application takes 200 to 500 hours." We sometimes create, or see others create, Access applications that require more than 1,000 hours.


The previous tables provide you with insight into how time on a project is distributed. If you add up the overhead numbers in Table 4.3 (design, administration, deployment), you'll see that the average overhead per project for the selected eleven applications was 97 hours, or 46 percent of the total using the percentage column. Many developers do not take these overhead items into consideration when they create a project plan and consequently end up with inaccurate time budgets.

Because the analyzed projects were not large and could not benefit from economies of scale, the overhead as a percentage was quite large. The percent of total project time spent on overhead decreases somewhat as projects get larger than those summarized here.


Factors that Constrain Project Budgets

Ideally, the project cost budget is a factor solely of the scope of work. In a perfect world, you would simply take the final specification and apply costs to the features. The reality is different, however; a project's pricing is affected by numerous factors, such as the following:


If you must participate in competitive bidding for projects, don't bid on projects that have an incomplete or vague specification-you will lose money. If you see weaknesses in the specification that you are asked to bid, show these shortcomings to the client and try to have the specification reworked. Both you and your client are put in better positions as the accuracy of the specification improves.


If you can afford to do so, run away from projects where users and/or managers have established the budget without the help of developers.

Regardless of early perceptions of the development budget, I feel strongly about researching and specifying a project as if the development budget were not an issue. In fact, I prefer to talk only about the budget for the design and specification phase until the spec document is done. This simply means that I try to convince clients to expend the time and money to first create a long-term project plan that meets the determined user and business needs, irrespective of cost.

Only with this approach are users assured that all their needs will be considered, discussed, and researched. In a cost-centric model, ideas are thrown out too early in the process strictly due to someone's determination that they are not economically feasible. If an idea is dismissed so capriciously, it does not even receive the benefit of the developer's wisdom in seeking alternative, less costly approaches to arrive at the same features, and the users lose out (see the sidebar).

A Penny Saved Is Ten Cents Wasted

To illustrate my point about the dangers of undercutting the specification budget, I will describe a situation I have seen many times in my consulting career.

For our average medium-sized Access projects, the research and specification work is completed in around 100 hours. Assume that in this amount of time, we can design a system that matches the users' needs. If, instead, we are forced into a more economical 40-hour design phase and charged with designing a system that matches the users' budget, the quality of the specification and the final product will be dramatically reduced.

In the small picture, the client thinks they have saved a few thousand dollars as a result of the 60 hours of deleted specification work. In the big picture, however, the client has cut off the research and discussion of features prematurely. In a year or two, information that was not included in the original spec will come to light, and the application will be changed at a cost equal to ten times the few thousand dollars initially saved.

Adjusting Features to Match the Budget

In cases where a budget amount is predetermined and the budget and features do not match, the developer is placed in a tricky situation. Having painstakingly worked with the design team to craft a good application plan, the developer must now consider changing or removing features from the current version of the application to meet a budget target. The developer has several alternatives to choose from when trying to deliver the feature set within the prescribed budget, such as the following:

Three Months of Designing, Ten Years of Coding


One of my most satisfying development projects was a (DOS-based) circuit management application we did for a telecommunications company. When the project started in 1986, I was able to convince the company of the importance of a good design phase, so most of their staff was involved with my staff in a series of design meetings that went on for several months. The philosophy we adhered to in the meetings was the one I describe in this chapter, where we assumed that we had a data problem to solve and an open-ended budget for solving it.

At the end of the design phase, we had produced a comprehensive plan that incorporated not only their current needs but everyone's best guess regarding their future needs. A budget was created that enabled us to deliver a majority of the desired features within the first year and to structure the application so that the nonimplemented features could be inserted later.

After deployment, the client called periodically over the next several years, as money became available, and asked us to implement more of the features from the original design. In each case, when we went to add the features, we were able to "hook" them in to the existing application easily because we had created an available "slot" for each future feature to occupy. For example, we placed fields in the first version of the data structure that we knew we wouldn't use for several years. Creating them up-front was cheaper than adding them after the system was in production.

Our foresight, and the client's, paid off handily as the application grew because new features fit into the design quickly and cost-effectively in a fashion unlike the all-too-common "gut and rebuild" approach that must be used to expand software not written with a growth potential built-in.

As I write this in 1996, we are still supporting and expanding this application guided by the original project spec, and it is still serving the client well after ten years of use.


Where possible, create a project budget as an amalgamation of component budgets with costing applied to each major element. With this process, you can easily mix and match features to meet the allocated funding for the project and budget for the future addition of any deferred features.

You can even apply project time for overhead items like testing and contingency at the component level.

Of course, budget compromises are never pleasant for either developers or users, but they are a fact of life.

Using Tools in the Design Process

From a mechanical standpoint, creating a design specification can be as simple as typing large volumes of information into a Word document. A better approach is to select the appropriate tool for each component of a spec document, and to combine the outputs from the tools into the final specification. This way, you can leverage the best features of a variety of tools as you try to achieve the following objectives:

Spec development is a two-way communication process between users and developers-users request various features and developers tell them what's realistic and affordable. Because the communication is two-way, it helps to communicate in a language that all parties understand. We prefer to use a toolset for spec authorship that is familiar to our users and clients, so we use Office and other widespread Microsoft products as much as possible. Thus, our clients usually own the same tools we use to create spec documents. The following are two benefits:

This section lists several of the components of a spec and the suggested tools you can use to create the component. It is not meant to be a reviewer's guide to all available tools for each specific task. Instead, I describe the tools that work best for us after employing a broad variety of methodologies over the years. If you disagree with my choice of particular tools, the examples here of various processes should still be of value to you, regardless of the tool you employ in the process.

Creating Database Diagrams

In my opinion, the business diagramming war is over and Visio won. I really like the product and I find it easy to use for creating almost any kind of business diagram, including navigation maps, process flow charts, and database relationship diagrams for specifications. Figure 4.5 shows a sample database diagram created in Visio.

Fig. 4.5

Visio is an effective tool for creating database structure diagrams.

One of Visio's strengths is the ability to use Automation to create drawings via program code. As an example, you can create a database schema in Access or definition in Excel then build a drawing of the schema in Visio using VBA code. Your code would simply automate Visio to rough out a diagram by programmatically creating a drawing object for each named table.

In the specification outline above, the "Navigation Map" topic refers to a flow diagram of a user's interaction with the application interface through forms. Such a diagram shows each form in the application and a visual depiction of how the user moves between the forms. The map's purpose is to show how the application branches and flows based on button clicks and menu/toolbar selections. Navigation map diagrams are also easily created with Visio, as shown in figure 4.6. (Refer to Appendix B, "Design Specifications-A Detailed Outline," for more information on navigation maps.)

Fig 4.6

You can use Visio or a similar drawing tool to create the navigation map diagram used in specifications.

Other options for creating simple drawings in your spec include the following:

In addition to using drawing programs, you can create database structure diagrams using database design tools, as described in the next section.

Creating Data Structure Documents

Your specification should include documentation for tables and fields. This information is best expressed in tabular format, so I standardized years ago on a system of building table definitions in Excel. The ease of printing, reordering, and outlining in Excel makes the process of schema design quick and simple, and we can e-mail Excel files to our clients for review with certitude that they own the program and know how to use it.

We begin by building a worksheet with columns for the most important Access data structure properties, such as Table Name, Field Order, Field Name, Data Type, and Description. Then, we or the design team populate the worksheet from the information gleaned in the research phase (see Figure 4.7).

Fig. 4.7

You can use Excel to create a database schema design worksheet.

Depending on your style and needs, you can expand the worksheet shown to include more detail about the data structure, such as Format, Validation Rule, Default Value, and other field attributes.


I prefer to order the tables and fields alphabetically by name when defining the schema but to place the primary key and foreign key field(s) at the top of each table listing. Reading through object names alphabetically is easier when reviewing the specification. I include a Field Order column in the worksheet so I can sort on this attribute just before the spec is finalized, to reflect the actual desired field order in the table.

Figure 4.7 also demonstrates the following benefits you inherit from using Excel for schema design worksheets according to our template:

The file SPECSHEL.XLS on the CD-ROM provides you with a version of our Excel design spreadsheet that you can use as a template for your project designs.

Using the power of VBA and Automation, my company has created a tool that takes our database designs in Excel once a specification is frozen and automatically builds the Access database from it. This saves us dozens of hours of schema-creation work per project. The code that provides this feature is discussed in Chapter 10, "Creating Expert Tables and Queries" and is included on the CD-ROM.

Of course, you have options other than Excel for creating the table structure documents in your spec:

Creating Form Mockups

The Access Form Wizard provides a handy tool for creating form mockups during the design phase. Members of the design team can even participate directly in this process because it is quite simple. Once the database structure is shelled-out in Access, run the Form Wizard against the structure to create draft form mockups. Drag the controls to their desired locations to make the mockups match the users' requirements. Alternately, you can create form mockups in Access before the tables are created by using unbound forms and controls.

On the form mockups, add controls to reflect the data source for the form. Add any additional text boxes, labels, command buttons, and other controls that make up the form and print the form mockup to include it in the specification. For mockups, you do not write any code nor set most form or control properties.

Of course, you can also do form mockups in drawing programs, such as Microsoft Paint. You can even do form mockups by hand, having members of the design team draw them out on paper.

Designing Report Mockups

As with forms, report mockups are easily done using the wizards in Access. I find three liabilities in such a strategy:

  1. Time Expended. Too often, when designers or developers try to mock up reports in Access, they tend to spend too much time beautifying the report and trying to get just the right look and layout. By the time the report mockup is completed, a significant portion of the development effort for the report has been expended, which pulls too much development time forward into the design phase.
  2. Communication. Most potential users of the application are probably not Access literate and therefore only a limited subset of the design team, and even fewer management-level reviewers, will be able to review and modify the designs online.
  3. Sample Data. Report mockups are clearer for reviewers if they contain sample data. For mockups built in Access to display sample data, tables must be built and populated. Again, the time investment in this effort is imprudent.

To solve these problems, I gravitate to one of two other tools: Excel or Word. You can lay out simple columnar reports in Excel very quickly using the cells and grids, and sample data with totals or other calculations are easily expressed. We give Excel report mockups to members of the design team to review and revise online because Excel is widely distributed and well known among our clients.

You can also create complex report layouts in Word documents and populate them with sample records by typing or pasting data from an existing system. It is as easy to send Word mockups around for review as it is with Excel files, with the further benefit in Word that each reviewer can make use of the revision marks feature to show his or her contribution to the final design.

As with forms, you can also design report mockups using drawing programs such as Microsoft Paint, drawing them by hand on plain paper, or even sketching them out on cocktail napkins!

A Picture Is Worth a Thousand Words


One of my clients years ago was a flamboyant and energetic entrepreneur who owned several small companies. In most of the systems we developed for him, he would be classified as the ultimate user of the application because reports that were run at almost any level in the company trickled up through the management layers and were reviewed by him.

Because this client spent so much time reading reports, he took a keen interest in the layout and content of each report. Near the end of each application design process, we would ask him to lead the design meeting for defining report layouts. In the meeting, he would review the data structures then go to the white board and draw in a frenzy, erasing sections wildly and alternately uncapping different colored pens to add emphasis to his masterpiece.


At the end of this spectacle, he would have produced a drawing of exactly what he wanted to see on a particular report. His administrative assistant would then enter the room, shoot an instant picture of the white board, and leave, at which time the client erased the board and began drawing the next report.

The photos were included in the specifications as the final report mockups for us to use during development!

Describing Code Processes

Code processes are usually described using a technique called "pseudocode," which simply means to describe a coding need without using application-specific terms. Every developer has a different style for writing pseudocode, and there is no single correct way to do it. The objective when writing pseudocode is to describe a process using terms that will be clear to both the nontechnical readers of the spec document and to the developers.

As an example, the process for exporting invoice records to an external billing system could be pseudocoded as shown in Listing 4.1. Note that one line in the listing does not map directly to one line of program code-it is up to the developer to make the translation between the English-like pseudocode and the programming language used to implement it. It is also up to the developer to create object names, add code comments, and place error trapping in the final code.

Listing 4.1 Pseudocode for a Billing Export

Create an export file name

Add "I" plus the current date as YYMMDD, plus a ".DAT" extension

Open the export file name for output

If the filename exists, prompt the user to overwrite

If No to overwrite, generate an alert and exit

Create a recordset with all non-exported invoice records,

sort the recordset by invoice number

If no records are returned, generate an alert and exit

Loop for each record in the recordset

Print the following items to the export file, separated by commas

Patient ID

Invoice date, in quotes

Invoice number

Invoice amount, with two decimal places but no dollar sign

Carriage return

Get the next record in the recordset

End of loop

Close the recordset

Close the export file

Alert the user that the export was successfully created

End

I prefer to create the pseudocode directly in the specification document. Writing pseudocode in Word is easy, and using a fixed-width font like Courier makes the indentation of the processes visually evident.


When the specification is approved, copy all the pseudocode routines to a new, empty Word document. Then, write a macro to go down line-by-line, adding an apostrophe to the beginning of each line. Now you can take the pseudocode and paste it into the Access module editor, using it as a guiding outline while you write your code. The leading apostrophes turn each pseudocode line into a code comment that is innocuous to the Access compiler. You can delete such comments at the end of the coding process when they have outlived their usefulness.

Preparing Project Timelines

Simple project timeline documents are often prepared in Excel worksheets. You can lay out milestone dates, expand and collapse outlines of related tasks, and print selectively with ease. Excel's weakness in the area of project planning lies in the difficulty of creating and maintaining dependencies on project dates.

If you are more adventurous, you can create your project plans in Microsoft Project or similar project management software. Project enables you to set up and maintain links between tasks, making it simple to slide an entire phase forward in time if one component of that phase is delivered late or postponed.

Project also makes it easy to assign times to each task and to sum the task times into time budgets by phase. See Figure 4.8 for a sample application development plan done with Project.

Fig. 4.8

Microsoft Project is useful for creating and maintaining application development timelines.

Using the Binder for Specifications

The Binder application in Office can be used to group some or all of the components of your specification documents. Because a specification can have many different components and the Binder works primarily with Excel and Word files, you may or may not be able to make use of this technology.

If you create spec components with software whose outputs can be embedded into Word documents as document objects (such as Visio drawings), you will be able to place Word files in your Binder whose purposes are to contain drawings or other outputs from other programs. With this technique, you can take advantage of the cross-file printing in the Binder.

From Here...

This chapter described in detail the process for turning design information into a written specification. Coupled with the spec template files SPECSHEL.DOC and SPECSHEL.XLS included on the CD-ROM and Appendix B, "Design Specifications-A Detailed Outline," you should now have the tools and techniques you need to create solid application specs.


1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.