Spreadsheet or Database?

Spreadsheets and databases, although originally two very separate application programs, have both evolved to have a few functions that duplicate those in either program. For example, spreadsheets can sort “records,” and do searches while databases offer some ready made formulas to calculate interest and other sums. Although an advanced user can usually go through gyrations that persuade either type of program to do some of the things that the other one does, there are some things which are easier in one program than the other. In order to take full advantage of electronic data it is necessary to be familiar with the functions of both types of programs.

The most important difference is how these two programs look at data:

A spreadsheet makes it easy to access an individual cell, thus its data, although in table format, can have different types of information in the same column or row.

A database, by contrast, is designed to derive information from "all" the data in a column either for that column alone, or any combination with other columns. To fulfill this purpose, the data is structured. Each line is a record and contains information pertinent to the same overall entity, e.g. a patient. Each column (called fields in a database) in the database contains the same category of information for each record. For example, one field will contain the phone number for the subject of each record.

A spreadsheet is a good tool for work that involves mainly mathematical calculations. A database, however, is far superior for managing information. The main difficulty with using a spreadsheet in place of a database is that one has to use a flat file design (see page 157 in the text) with all the attendant problems that this creates.

Item	Spreadsheets	Databases
Use of memory	Data and calculations are RAM resident, if power is lost to the computer and the file was not saved prior to this data is lost	Uses both RAM and hard drive memory, data is saved to the hard disk as soon as a the data enterer leaves the record thus if the power goes out, only one record is lost.
Organization of Data	Data cells are independent, formulas can specify specific cells by name. Formulas access one cell at a time.	Data is organized by fields or records. Formulas or queries work on specified fields in a record, or on specified fields for all selected records.
Calculations	Setup is easy Wide selection of financial and statistical functions	Limited to usual calculations but includes date arithmetic. Done using forms, queries, or reports.
Charts	Wide choice of charts and graphs. Easy to setup and easy to change.	Selection is good, but limited in comparison to spreadsheet. Data can be exported to spreadsheet to do a chart.
Reports	WYSIWYG, prints tables as are seen on the screen. Difficult to combine data from multiple spreadsheets.	Reports can be designed to serve the information needs of the user, does not need to look like the table. Each field can be placed anywhere on the report, and used multiple times. It is easy to combine data from more than one table into the same report.
Data Entry	Has limited ability to design forms and create “look up” tables (A look-up table provides options from which the data enterer can select.). Not as easily done, or as flexible in this area as a database. Some validity checks possible.	Possess excellent ability to design special forms that show only fields needed and include instructions on how to enter the data. Can also contain read only fields. Look-up tables can streamline data entry as well as improve data accuracy Forms can allow data entry into one table or many on the same screen. It is easy to provide validity checks on data to prevent entry of erroneous data by using look-up tables, drop down menus and other data validation features.
Programming Languages	Terse - hard to document and hard to read Hard to debug Suitable for short subroutines or macros	Full featured programming language with excellent documentation and debugging capabilities.
Queries	Limited queries especially for multiple tables.	Can perform very complex data queries on one or multiple tables. Can use set theory.
Relational Tables	Can link spreadsheets, but has many limitations.	Links many different tables easily so appear to user as one table. Very useful in transforming complex data.

Some excellent resources, some are old, but given that the structures of both spreadsheet and databases remain the same, the reasons for using which one remain the same.

Return to Chapter 9 Databases

Created November 19, 2011