Intelligent Systems
Volume III, Number 3 August, 1996

A quarterly newsletter for clients and friends of Chenault Systems

The following article is directed to beginning computer users who need to clarify the differences between a spreadsheet product, such as Excel® or Lotus®, and a desktop database product such as Access®, dBase®, FoxPro®, Paradox®, and R:Base®.

Spreadsheets and Databases
By Tom Chenault

Spreadsheet tools were around long before PC's first appeared in the office. Back in the 1970's and early 1980's, there were mainframe and mini computer languages, such as BBL, Profit II, IFPS, Model (the coolest), Express, etc. that provided financial modeling type spreadsheets. "Decision support system" was the big pitch of that era. The spreadsheet (or financial modeling) products were used for data analysis while databases, such as Focus, System 2000, IMS, DB2, and Total, were used for data processing and retrieval. In those days far fewer people were exposed to computers. In most cases, only computer professionals had to understand terminology such as "database" and "spreadsheet." Most applications were placed in the proper category.

When the PC was introduced in the early 1980's, products such as VisaCalc, Lotus (on the PC) and Excel (first on the Mac then PC) became household words and, to a great extent, facilitated the sales of desktop computers. Nearly everyone learned how to use these remarkable easy-to-use products and saved an enormous amount of time producing financial management reports. More and more business professionals became self sufficient and were less dependent on in-house information systems departments. Information could be down loaded from mainframes to Lotus and Excel. When database products became available on PC's, non-computer professionals became even more independent.

Nearly every month we see spreadsheet products, such as Excel or Lotus, being stretched to their limits when perhaps the application would be better placed in a database system. This is hardly a surprise when one considers the computer professionals, who in the past had determined what data best fit a spreadsheet and what best fit a database system. Some of these people were left behind with the mainframes, leaving users to fend for themselves on the PC. Spreadsheets have grown with sophistication over the last fifteen years, but users need to be careful in how they are used. Excel and Lotus store calculation formulas in the worksheet with the data. The worksheet is only a two dimensional matrix. Database tools offer multiple tables (oftentimes files) to store larger amounts of data, providing for all intents and purposes an infinite number of rows (records) and columns (fields), only limited by the amount of computer resources or the database manager. Database managers, such as Access or FoxPro, store formulas in update screens and reports, but not in the tables where the data is stored. The primary job of the spreadsheet is the analysis of small amounts of data. The primary job of a database product is the accumulation and processing of large amounts of data. Database systems have the ability to report on all or portions of the data through related queries, screens, and reports. Typically spreadsheets are used for financial projections and databases store historical information. For example, we use Excel for our vital cash flow projections. We try to project client payments, along with cash outflows, to forecast ending cash balances from month to month. We use a database system to accumulate transactions, such as time and billing information. Database systems are always used for custom built accounting systems. For example, an accounts payable system could have one table to accumulate vendor invoices with another "related" vendor table. The vendor table will contain fields, such as vendor number, vendor name, address, etc. and would be considered a master lookup or validation file. The invoice table, considered the main transaction file, is related to the vendor table via the vendor number. For example, the below vendor named "Caprock Janitorial" is related or linked to the below invoice table via the vendor number of "102." The character string "Caprock Janitorial" is only stored in the database in one place instead of multiple places.

Vendor Vendor # Vendor Name 101 Llano Printing 102 Caprock Janitorial 103 Anderson, CPA 104 Lubbock Real Estate Invoice Vendor # Inv. Date Invoice # Amount 101 06/24/96 cvd-3341 802.78 102 06/26/96 1278c9tc 111.53 102 06/27/96 1487 404.13 104 06/28/96 WX156Y 300.02

For validation purposes, no invoice can be entered from a screen into the invoice table unless the vendor number is already entered into the vendor table. This is one of the main concepts behind relational databases. Also, redundant vendor names will not be repeated over and over again in the invoice tables. This insures optimum system speed and storage, not to mention data accuracy and validation.

The primary job of the spreadsheet is the analysis of small amounts of data. The primary job of a database product is the accumulation and processing of large amounts of data. Database systems have the ability to report on all or portions of the data through related queries, screens, and reports

Tables are "indexed" to speed up data lookups. For example, the index for the vendor table would be the vendor number. The index for the invoice table could be a combination of vendor number, invoice date, and invoice number to insure the uniqueness of each invoice transaction. Database products are backed up with extensive programming languages to allow for complex business situations. For example, with the new Visual FoxPro, you can use Visual Basic or the xBase programming standard to handle complicated reporting or validation procedures. FoxPro, along with other database systems, have the Structured Query Language (SQL) for those queries or reports to select records based on certain screening and sorting criteria. Access uses both Visual Basic and SQL. Spreadsheets use "macros" to handle procedures that are repetitious, such as the consolidation or the adding together of multiple worksheets after one data item has been changed (what if analysis).

In summary, a great deal of applications start out as a spreadsheet prototyping the resultant database application. No effort is really lost. The following are attributes to look for in deciding whether to use a database product or a spreadsheet product:

Spreadsheet

· Analysis of data

· Small amount of data

· Simple validation

· Matrix oriented

· Future projections

· Finite number of rows and columns

Database

· Processing, reporting and storage of data

· Large amount of data

· Complex and strong need to validate data

· Table (relational) oriented

· Historical transactions

· Infinite number of rows (records)

Quote Worth Noting
"A man's mind, stretched by a new idea, can never go back to its original dimensions." -- Oliver Wendel Holmes

Copyright © 1998-99 by Chenault Systems, Inc.
All rights reserved.
Click here for additional legal and trademark information.