What You Better Know About Databases

If you had a choice, would you use a wrench to hammer in a nail? Although it can do the job in a pinch, a wrench isn't designed for hammering. If you insist on using one no matter what, you may end up with a bent nail and a bruised finger.

A similar disconnect occurs when spreadsheet software is used for database jobs, a common practice among accountants. CPAs' affinity for spreadsheet software is understandable: Second only to word processing and maybe tax preparation software, spreadsheet applications have been one of CPAs' tools of choice since the introduction of the personal computer in the 1980s. That preference has increased steadily because spreadsheet packages have grown so powerful and adaptable that they now are able to perform special database functions and, in some cases, can analyze data even better than databases. Also, a few users have figured out complex workarounds to perform sophisticated database functions in spreadsheets that weren't ever dreamed of by the application designers. For more on how to choose between using a database or a spreadsheet, see the sidebar Should You Use a Database or a Spreadsheet.

The fact remains that databases are powerful tools that can do many jobs spreadsheets can't, and the goal of this article is to convince you that it's time to add a database application to your computer tool arsenal.

WHY A DATABASE?
Once you learn how to use a database, you'll discover the projects that were complex and arduous on a spreadsheet are much easier to handle in a database, often needing only a few mouse clicks. Following are some of the key reasons databases are very useful:

• Data independence. Once data are entered into a relational database grouped in tables, they can be combined in myriad ways. The software takes data in one table and relates that information to data in another table, hence the name relational database.

If a database contains information on a company's customers in one table, its products in a second table and the history of what each customer buys in a third table, their interrelationships can be used to produce a wide range of relational information. For example, it can produce a list of customers who buy only product A, a list of those who buy only product B, a third for those who buy only product C and yet another for those who buy only A and B.

Each data table is complete in itself and reports produced by the software will not affect the tables' makeup. Therefore, data can be combined for totally different purposes without copying or otherwise disturbing the underlying information.

To illustrate the relational database concept, exhibit 1 using Corel's Paradox 8, shows a relatively simple data model. In this example, the database uses four tables and the relational aspect of the database is demonstrated with arrows. A double-headed arrow indicates a many relationship with the data, while a single-headed arrow represents a one relationship. In a many relationship, a given customer (customer table) can place many orders (orders table); each order (orders table) can have many line items (lineitem table); and each line item (lineitem table) is associated with one inventory item (stock table). (Database designers label tables with strings of combined words because many database programs require that tables be identified with one word only. Hence the lineitem table.

• Data sharing. Because related databases can easily generate ad hoc reports, information can be shared among many users. For example, a human resources administrator and a payroll supervisor can share the same employee data, yet each would relate the various tables in different ways. This feature ensures data integrity and eliminates data redundancy. For example, the customer's name is stored only once, in one table; ditto the customer's address. If the customer's address changes, it needs updating in only one place.

In addition, users can customize the screen displaying a report so only selected data appear; also, those selections can be customized for different viewers. For example, look at exhibit 2 (a screen generated with Microsoft Access), which illustrates how the same underlying data can provide customized views for different users. At the top of the exhibit is a view designed for a clerk who needs only limited information about the subject, while the view at the bottom shows all the data about the subject, something a supervisor might need. The screen shot at the bottom shows how that same data can be assembled as a table. In addition, each view can be password-protected for security.

• Data accuracy. Input data screens can be designed to accept information only if the data are formatted in a particular way, a technique called validation checks . For example, a validation check could be set on the Hourly Rate field in exhibit 2 so only numeric entries are allowed, the numbers must be positive and the rate must be between $5.50 and $12.50. If a user tries to enter any other information in any other format, it's rejected.

• Ad hoc queries. Another feature that makes a database powerful and convenient is its ability to accept ad hoc queries. For example, there is no way the developer and designer of a database application can know ahead of time exactly how the information will be used or what questions the database will be asked. One person, for example, may want to know how many units of product A have been sold to women in New York, while another user might want to find out the profit margin of product B in California. Database applications allow users to set up an infinite number of queries, and as long as the underlying data exist and relationships can be ascertained, the software can report those relationships.

• Report generation. Most databases come with predesigned templates for commonly used reports, but it's easy to create new ones. Once a template is created, say for a monthly report, you can generate update reports with a few mouse clicks.

• User-friendly development tools. While the newer database programs are designed for ease of use, you'll need some training for nearly all database applications, even the ones that promise you can start using them the moment you get them out of the box. Most come with handy one-on-one teaching programs (assistants or wizards) that walk you through a series of steps, and provide a range of design options for creating customized queries, forms and reports. These intuitive development tools make it relatively easy for you to handle sophisticated projects.

• Database size. While spreadsheets can manage about 5,000 records, databases can effectively handle up to a million.

• Importing. Most database applications allow you to open, use and store files in many formats. Thus, databases can handle pictures, graphs, audio, video, spreadsheets and word processing documents, all of which can be incorporated into reports. So, for example, a human resources database can include photographs of employees in addition to all the other text and numerical data.

For help in choosing the right package for you, see the following article, Choose the Database for You.

As you can see, databases are powerful tools. To use them effectively, you must invest some time and learn how they work. It's never easy learning new skills when you think you can get by with your old ones. But the reality is that, as business gets more complex and you're called on to provide more sophisticated information to develop business strategies, you will need new tools.

Which is why it's time to think databases.

Should You Use a Database or a Spreadsheet? The answer to this question is becoming less clear because today's databases and spreadsheets have become so powerful that now they offer many similar functions. However, it's their differences that count when you've got to make a decision on which to use. If a single user, say, a financial analyst, cost accountant or auditor, needs a productivity tool to facilitate ad hoc what-if analyses, a spreadsheet is likely the better tool. Very sophisticated spreadsheet users might argue that they can program most database features into spreadsheet applications. Though true, such applications are typically complex, poorly documented and require much maintenance. In most situations, you should consider at least three factors when deciding whether to use a database or spreadsheet: data structure, data sharing and data validation. THE STRUCTURE OF THE DATA Data structure refers to the nature of the relationships among data items. For example, assume you capture the following data items pertaining to your company's sales: customer name, age and sex; salesperson name; sales region; sale date; product name; gross sales; and cost of goods sold. Assume further that you plan to use the data in the same way every month: You want to produce a report on operating income (gross sales minus cost of goods sold) in total, by product and by salesperson. If you rarely change the way in which you want to view the data, the data structure is considered relatively static and a spreadsheet might do the job. Notice we said might . The decision tree below can help you select the right application for a project. Note that the size of the database is not incorporated into the decision tree, because if there are more than 5,000 records of data, the best solution almost always is a database. Now assume you are not quite sure how you will view the data in the future, but you want to be able to sort and filter the data in various ways on an ad hoc basis. For example, you might want to determine the profit margin of a certain product purchased by female customers between the ages of 30 and 45 in the northern region that were sold by salesperson A. Dynamic queries of this nature are difficult to construct using a spreadsheet; a database, however, can perform such queries quite easily. SHARING DATA Data sharing refers to the number of users with access to the data. For example, assume that sales transaction data are maintained by the accounting department and that all sales reports must be processed through accounting. In that scenario, data sharing is relatively low, which would be reason to think a spreadsheet could do the job. Now assume that employees in the accounting, sales and inventory departments can perform ad hoc queries on the sales data. Data sharing in this situation is considered to be relatively high, and that should point to using a database because, unlike the spreadsheet, the underlying information in the database remains undisturbed and safe. CONTROLLING THE DATA Data control refers to the extent of input editing and validation to be performed on the data. For example, assume that authorized personnel from engineering, production, sales and accounting can enter estimates and assumptions into a forecasting application. Given the fleeting nature of forecast data, there may be little concern over data control; hence, control is considered relatively low. In addition, the underlying data will probably change and what-if scenarios will be created that would lead you to consider a spreadsheet. Now consider the organization's sales transactions. It's necessary to be very careful when entering sales transactions to ensure the accuracy and integrity of customer billing and related financial statement accounts. Data control, in this situation, would be considered high, and that should steer you toward a database.