Database vs. Spreadsheet
 

When do you use a spreadsheet, and when do you need a database? Both application types are used for managing data. How do you decide which would be more practical?

Spreadsheets

Beforethe computer, bookkeepers, record keepers, and accountants used the paper andpencil method along with a ledger or record book containing worksheets. Information and records were stored by hand and financial records werecalculated manually and entered in to the worksheets.  Ledgers used rowsand columns that people learned could be used not only for financial records,but also for things like scheduling, inventory tracking, and employeeinformation.

Thedawn of the computer age brought applications that could store information,perform complex calculations, and provide a printed output. This conceptvirtually revolutionized the use of the computer early on. The first application with any real power was Visi-Calc, which laterbecame Lotus 1-2-3.  Visi-Calc alone gave businesses a serious enough reason tojustify investing in computers for the office and is actually credited withkeeping Apple computers in business.  Theadvantages of using a spreadsheet application rather than paper and pencil werenumerous - not the least of which was that when data was changed, totals andother formulas were automatically recalculated, saving both time and headache. However, Visi-Calc lacked functionality in the way of tools available to theuser.

Currently, Microsoft Excel,along with Lotus 1-2-3, commands most of the market for spreadsheetapplications.  Tools have evolved tremendously since that first Visi-Calcprogram.  Now users have help available at a click of their mouse along with toolssuch as complex formula support, formula and function builders, sorting andfiltering, scenario managers (for "What-if" analysis), charts andgraphs, and extended data formatting tools.

Databases

Adatabase organizes information on a particular subject for retrieval.  Databases utilize one or more tables of information entered bythe user to retrieve data for a variety of purposes.  Data can be retrievedthrough methods such as asking questions of the data (querying), sorting orfiltering, and pulling information into a formatted report, like an invoice, that canbe printed.  Although the tables look similar to spreadsheets, the tablesare used to store raw data. In other words, there is no need to format theinformation in a database table.  Reports generated from the data in thetables are where you would want specific formatting.  Information in aspreadsheet is formatted in the actual spreadsheet, and that makes data entry abit more tedious.

Databasesalso involve the use of records to structure the tables.  A recordcan contain any number of fields.  Comparing this to a common phonebook, a record would be an entire entry for one individual, and a field would beeach separate part of the entire entry - like the individual's phonenumber.  Reports organize the information in an understandable way and cancombine data by performing complex calculations.  Databases can also easilymanage a large amount of information and better maintain data integrity. Forthese reasons, databases are much more powerful and manageable when handling a large amount of information related to a particulartopic.  

The downfall?  The downfall is that most database programs arenot as easy to learn and use as most spreadsheet applications and are not as easy tomake structural changes in once queries, forms, and reports are developed.  One must have knowledge of the best way tostructure the information into one or more tables before any tables are used todevelop the means of retrieving the information.  The reason for this, is that once saved queries, forms,and reports are based on the table(s), any changes in the table(s)structure (like deleting/changing field names) may cause errors in all theobjects based on the changed table(s).  So, it is important that thedeveloper of the database has a clear vision of all types of information thatwould need to be included and how to organize it.  This, combined with aninterface that's not usually as intuitive as a spreadsheet, sometimesintimidates would-be database users.

AnExample of Database vs. Spreadsheet Use

Nowthat you are a bit more familiar with the purposes of the two, how do youdetermine which is best for your data?  Most businesses find that using bothworks best.  Take a look at the following simple example:

CompanyABC needed a method of storing data related to customer sales where they couldprint invoices and be able to track orders and customer contactinformation.  They also needed to be able to quickly calculate what anincrease or decrease in product prices and/or sales would do to their overallrevenue generation along with a way to analyze trends.

First,they developed an Access database to store all of their customer information andordering data.  They included the following tables: Contact Information,Products, and Orders.  From this they used the tables and also createdqueries of the table data on which to base reports, like invoices.  They alsocreated easy to use forms for inputting data and a user-friendly switchboard foreasy navigation.  This gave them an efficient way to enter data, storedata, and generate information for invoices, sales by product, sales bycustomer, and so on.

Second,they used Excel spreadsheets to quickly calculate what changes in price andsales would do to their revenue by creating various scenarios.  They couldalso use their sales information in Excel to analyze trends by generating chartsand graphs.  This gave them an easy way to analyze their data and trends ina tool with understandable and meaningful formats.

Whatcan we gain from this example?  As a general rule of thumb, databasesshould be used for data storage and spreadsheets should be used to analyze data. 

Ifyou currently use a spreadsheet to store data, ask yourself the followingquestions:

  • Do changes made in one spreadsheet force you to make changes in others?
  • Is the sheer amount of data unmanageable or becoming unmanageable?
  • Do you have several spreadsheets that contain related information (such as separate sheets with sales for branches in Los Angeles, Chicago, and Houston)?
  • Can you see all relevant data on one screen, or do you have to keep scrolling to find information?
  • Are several people accessing the data at the same time?
  • Do you have a difficult time viewing specific data sets that you want?

If you answered yes to at least two of thequestions, you should think about moving your information to a database application.

Ina Nutshell

Use a databaseif... 

  • the information is a large amount that would become unmanageable in spreadsheet form and is related to a particular subject.
  • you want to maintain records for ongoing use.
  • the information is subject to many changes (change of address, pricing changes, etc.).
  • you want to generate reports based on the information.

Use a spreadsheet if...

  • you want to crunch numbers and perform automatic calculations.
  • you want to track a simple list of data.
  • you want to easily create charts and graphs of your data.
  • you want to create "What-if" scenarios.

In most cases, using a combination of a database to store your businessrecords and a spreadsheet to analyze selected information works best.

About.com
The Human Internet
http://businesssoft.about.com