Most people are familiar with some kind of spreadsheet, such as Microsoft Excel. Spreadsheets are easy and convenient to use, and they may be employed by an individual much like a database is used in the enterprise. Let's look at the features of spreadsheets to see how good of a database tool they actually are.
Similar to databases, spreadsheets are commonly used to store information in a tabular format. A spreadsheet can store data in rows and columns, it can link cells on one sheet to those on another sheet, and it can force data to be entered in a specific cell in a specific format. It's easy to calculate formulas from groups of cells on the spreadsheet, create charts, and work with data in other ways. But there are many ways in which a spreadsheet is not like a traditional database table:
Spreadsheet | Database |
---|---|
More than one datatype can be stored in a spreadsheet column. | Usually, only one datatype can be stored in a database table column. |
Cells in a spreadsheet can be defined as a formula, making the contents variable depending on other cells. | Columns in a database table have a fixed value. |
A spreadsheet has only the physical row number to make it unique and no built-in way to enforce uniqueness of a given spreadsheet row. | Single rows of a database table are uniquely identified by a unique value (typically a primary key, as described later in this chapter). |
Usually, only one user can have write access to the spreadsheet at any given time; anyone else is locked out, even if the second user is on a different part of the spreadsheet. | Multiple users can access a database table at the same time, with various combinations of read and write capabilities in different parts of the database. |
A spreadsheet does not have any built-in transaction-control capabilities, such as ensuring that a group of changes to the sheet is completely applied or not applied at all. The Save button is about the best a spreadsheet can do to simulate transaction control. | A database usually has transaction-control capabilities, making it possible to "roll back" a change if something happened to prevent it from completing successfully (such as a power failure). |
A corrupt spreadsheet cannot usually be repaired; the entire spreadsheet must be restored from a backup, which may have occurred yesterday, last week, or never! | There are many tools for repairing and recovering databases. |
This is not to say that a spreadsheet isn't a valuable tool in the enterprise for ad hoc and "what-if" analyses. Furthermore, most spreadsheet products have some way to connect to an external database as the data source for analysis.