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.
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.
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 in a currency field 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 pre-designed 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.
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.
This is why it’s time to think about 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 towards a database.
