INTELLIGENT SYSTEMS
Volume XIII, Number
Copyright © 2006 Chenault Systems, Inc. All rights reserved.
Adapt Update
As we continue to serve the trade show industry, ADAPT has
been made to interface with many registration vendors, such as: ExpoExchange,
CompuSystems (CSI), AccuReg (ARI), AR Systems (ShowCare), and InfoSalons.
The link below contains a demonstration of ADAPT developed
by Chenault Systems and Hanley-Wood during 2001-2002. This product won "Trade Show Innovation
of the Year" in 2003. The
system takes data from all kinds of formats and aggregates it into ONE central
database. The system can also be used
over an Intranet.
By Tom Chenault
This article has been brought back and revised from our August 1996 newsletter at the request of some of our followers and is directed to persons who need to clarify the differences between a spreadsheet product, such as Excel® or Lotus®, and a desktop database product such as Access®, FoxPro®, SQL Serve®r, Oracle®, or R:Base®.
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 products, such
as BBL, Profit II, IFPS, Model, Empire, and Express, etc. that
provided financial modeling type spreadsheets.
“Decision support system (DSS)” was the big pitchbuzzword
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” and most applications were placed in the proper
category.
When the PC was introduced in the early 1980’s,
products such as VisiCalc, 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 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.
However, nearly every month we see spreadsheet
products, such as Excel or Lotus, being stretched beyond their limits when the
application would be better placed in a database system. This is hardly a surprise when one considers
it was computer professionals who determined what data best fit a spreadsheet
and what data 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 in sophistication
over the last twenty 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, store formulas in functions, update
screens and reports, but not in the tables where the data is
stored. The key is the
functionality
is separated from the data in a database manager. The primary job of the spreadsheet is the analysis
of small amounts of data. The
primary job of a database system 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 while
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 should
always be used for custom-built financial
systems.
For example, an accounts payable system could have one
table to accumulate vendor invoices with another “related” vendor table. The vendor table will contains
fields, such as vendor number, vendor name, and address,
etc. and would be considered used for 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 |
|
|
Invoice |
|
|
|
|
Vendor # |
Inv. Date |
Invoice # |
Amount |
|
101 |
|
cvd-3341 |
802.78 |
|
102 |
|
1278c9tc |
111.53 |
|
102 |
|
1487 |
404.13 |
|
104 |
|
WX156Y |
300.02 |
For validation purposes, no invoice can be entered
from a screen into the invoice table unless the vendor is already in the vendor
table. This is one of the main concepts
behind relational databases. Also, redundant
vendor names will not be repeated over and over againare
not in the invoice tables. This insures optimum system speed and storage,
not to mention data accuracy and validationintegrity.
The primary job of the spreadsheet is the analysis of small
amounts of data. The primary job of a
database system is the accumulation and processing of large
amounts of data.
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 beis a
combination of vendor number, invoice date, and
invoice number to insure the uniqueness of each invoice transaction.
Database
products are backed supportedup
with extensive programming languages to allow for complex business
situations. For example, with Access, you
can use Visual Basic or Structured Query
Language (SQL)SQL procedures can be used
with SQL Server to handle process any
complicated reporting or validation procedures.
Typically, Access is the user-friendly front
end for forms, queries and reports and while
SQL Server is the “back office” for industrial strength data
maintenance in the form of tables. FoxPro, along with other database systems, have the Structured Query Language (SQL) SQL for
those
queries or reports to select records based on certain screening
and sorting criteria. Spreadsheets use
“macros” (spreadsheets have functions too!) to handle
support procedures that are repetitious
processes, such as the consolidation or the adding together of
multiple worksheets after one data item has been changed (what if analysis).
In summary, many applications start out as a
spreadsheet, prototyping the resultant database
application. Any lost effort is
normally offset by the proof of concept the spreadsheet provides.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 |
|
·
Limited
security |
|
·
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 |
|
·
Full security |
|
·
Infinite number
of rows (records) |
Access® is a registered trademark of
Microsoft Corporation
Excel® is a registered trademark of Microsoft Corporation
FoxPro® is a registered trademark of Microsoft Corporation
Lotus® is a registered trademark of Lotus Development Corp. a subsidiary of
IBM Corporation.
R:Base® is a registered trademark of
R:Base Technologies
SQL Server® is a registered trademark of
Microsoft Corporation
Quotes Worth
Noting
“The overly bureaucratic Sarbanes-Oxley
(SOX) processes could lead to the end of global domination by the
Brooks' Law: Adding manpower to a late software
project makes it later. (Frederick P Brooks)
To unsubscribe please
reply with “unsubscribe” in the subject line.
Chenault Systems, Inc.
972-306-3839