by Jo K. Horan
During the last few months, at the Alpha Four / Alpha Five SIG meetings devoted to database design workshops, we’ve had a lot of questions regarding the different ways in which you may want to look at your database information. In an earlier article in this series, we printed some simple table examples in “spreadsheet” format. This is the easiest way for most newcomers to visualize a database table. Everything is neatly arranged in rows and columns, and you can scroll up and down, or left to right, to see any data that won’t fit on a single screen. In database terms, this type of layout may be called a “worksheet” or a “browse” format, but the name doesn’t really matter. It’s the style that counts. The plain-vanilla version of this layout shows you all of a single table’s fields and records, in left-to-right order as the fields were originally defined in the table, and from top to bottom in the sequence in which the records were initially entered into the computer. Figure 1 illustrates this format, displaying the first few fields from a handful of records from a typical name-and-address list. To see the remaining fields, you would need to scroll to the right, and to see the rest of the records, you would have to scroll towards the bottom.
A good database software product will give you ways to modify this default layout, if you wish to. You should be able to eliminate columns from the layout, re-size and re-order columns you want to keep, and create as many customized browse layout formats as you need. This lets you display most of the fields you want to see at any one time on a single screen, without having to wander back and forth. You may be allowed to spell out or otherwise enhance the field names or column titles at the top of the layout, to better identify or describe the data you’re looking at. You should also be able to “search and sort” your table in one or more ways, so that as you scroll from top to bottom, you see only those records which contain the information you’re looking for, and in a sequence which makes it easy for you to recognize. This approach is good for first-cut scans of critical data, so that you can quickly find the records you need to answer specific questions.
Once you’ve found the record you’re looking for, you may need additional information. Other fields in the record may be helpful in answering your questions, even though you didn’t need to see all those fields in order to select the right source record. This is where a “form” layout comes in handy, because it gives you a close-up view of a single record. You can use a browse table to find the right record, and then switch to a form view to see details within that record. A standard default form will show you all the fields in one specific record, listed top to bottom on the screen, in the sequence in which the fields exist in the table and identified by the original field names. Figure 2 shows all the information from the first browse table record, in a typical default form layout style.
Again, as in the case of browse tables, you may want to make some changes to this default form. If the vertical layout runs to more than one page on the screen, you might want to arrange some of the fields in parallel columns in order to take up less space, or you could put important fields together on the first screen and move less significant fields to later pages. You could also replace cryptic field names with unabbreviated or more descriptive titles, and you could group related fields together in a logical way, leaving enough blank space between groups to make the layout visually appealing. (This also helps you to avoid making mistakes or misinterpreting what you see.) For data entry purposes, it’s best if you can design your form layout to closely match the printed document from which you’re taking the input information. And to respond to specific questions, you may decide to organize the various fields in different ways, so that as soon as you view the right record you know exactly what your answer needs to be. Figure 3 shows the same information as Figure 2, but in a more attractive, customized format.
Browse tables and form layouts such as those described above are normally viewed on the screen, but they can also be printed out on paper for reference if needed. Another typical layout, which we call a “report”, is usually designed to be printed on paper, but reports can be helpful if they can also be displayed or previewed on the screen. Reports allow you to separate “primary” from “secondary” data within the same record, printing significant fields on the first line and arranging subordinate information on subsequent indented lines. In addition, reports usually let you “group” data according to the values in one or more fields, either on the same page or on separate pages, so that different categories of information can be split apart. For example, an alphabetically sorted series of records can be separated by “header” lines that indicate which letter of the alphabet you’ve reached, making it easier to find and identify the information you’re looking for. Reports also allow you to calculate numeric totals or averages, either by groups or for the report as a whole, and you can look for maximum or minimum values, frequency counts, and first and last entries. Figure 4 is an example of a simple report, grouped on one level by membership category.
All of these layouts can be used for individual tables, and they are adaptable for relational configurations which include multiple tables, like those shown in last month’s diagrams. You can design your layouts so that the average user never needs to worry about what the underlying database structure actually looks like. In addition, you can often make use of special-purpose layouts, such as form letters which incorporate “personalized” information, mailing labels that include automatic bar codes for zip codes in addresses, and other formats that are often found in word processors or desktop publishing programs. Your goals for layout design should be based on the following three principles: (1) to make data entry easy and accurate, (2) to answer client questions directly and concisely, and (3) to summarize your data correctly and conveniently. When you achieve these goals, you’ll know you’ve created a well-designed database application! To see actual illustrations of all of these types of layouts, plan to join us at HAL-PC headquarters at 6:30 p.m. on the 2nd Monday in November and we’ll show you hands-on examples of what these layouts look like and how they work, so that you can create your own versions using whatever database software you decide to use. We’ll hope to see you on November 9th!
Jo K. Horan is a database developer, consultant, and trainer, specializing in Alpha Four and Alpha Five. She is leader of the Alpha Four / Alpha Five Special Interest Group within HAL-PC, the Houston Area League of PC Users. She may be reached at 713-723-6709 or by mail at DataBasics, 9806 Cliffwood, Houston, TX 77096. n
E-mail me at webmaster@hal-pc.org with any comments you have and tell me what you want to see here.