Database Design Based on the Database Software

   

---

Spreadsheet Design

A spreadsheet is certainly a good tool if you use it properly and for the purpose for which it was designed. For example, it is quite suitable for work that involves complex mathematical calculations and statistical analysis. Contrary to popular myth, however, a spreadsheet does not make a good relational database. If your organization has a need to collect, store, maintain, and manipulate various types of data, then use the proper tool for the job by designing and implementing a real database. For example, consider the spreadsheet in Figure 14.2.

Figure 14.2. An example of a typical spreadsheet "database."

This spreadsheet is being used to keep track of store managers for a small chain of retail stores. As you can see, this approach has problems as well.

• Duplicate fields . Each field on this spreadsheet is a duplicate field. If you take the fields at face value, there are basically three fields in each instance: S TORE N UMBER , M ANAGER N AME , and A SSISTANT M ANAGER N AME .

• Multipart fields . Each field holds two values. The first field stores the store number and phone number, the second field stores the manager's first and last name, and the third field stores the assistant manager's first and last name .

• Multivalued fields . The A SSISTANT M ANAGER field is a multivalued field because there can be more than one assistant manager assigned to a particular store.

• This type of database is difficult to use. Data-oriented tasks that can be performed with ease in an RDBMS program are tedious and time-consuming to carry out in a spreadsheet. For example, it would take you some time to create a list containing only the name of each store manager and his or her phone number.

After seeing the problems associated with a simple spreadsheet "database" such as this one, you can imagine the types of problems you would encounter with a more complex database. If you're currently using a spreadsheet as a database, you can improve the database's quality, speed, and versatility if you remove it from the spreadsheet, take it through the entire database-design process, and implement it in a suitable RDBMS.

Dealing with the Spreadsheet View Mind-set

When you begin to work with a true database and RDBMS, you must break away from a spreadsheet view mind-set. This means that you'll have to resign yourself to the fact that certain ways of viewing the data are now unavailableyou can no longer use typical spreadsheet layouts. For example, consider a typical spreadsheet report shown in Figure 14.3.

Figure 14.3. An example of a typical spreadsheet report.

You cannot produce a report with this type of layout using a database. Whereas a spreadsheet stores the data exactly as you see it on the report, a database would store it in four separate fields within a table. Figure 14.4 shows an example of a database report you could generate for the same data. The database presentation is not the same as the spreadsheet presentation, but it is just as clear.

Figure 14.4. An example of a typical database report.

The point to remember is that you'll have to adjust the manner in which you think about working with the data in your database. In the end, there are far more advantages to storing and using your data in an actual database than trying to use a spreadsheet in a similar manner. A database gives you much more control over data integrity and the consistency and validity of the data. It also provides an almost unlimited number of ways to retrieve the data, enabling you to obtain a wide variety of information.

---

Top