Why a Spreadsheet is not a Database
It’s not uncommon to hear a company or organization refer to their “database” when they are actually talking about an Excel spreadsheet. Two clients recently showed me large complex spreadsheets that were being shared and updated by multiple staff members via email and network shares. In one case, a donations spreadsheet had more than 30 columns and was completely unmanageable. In the second case, a spreadsheet was shared between 4 office staff who had to constantly save and close the spreadsheet to take turns editing it. In both cases the spreadsheets had become a major bottleneck in the management of critical information.
So why exactly isn’t a spreadsheet the same thing as a database? There are some similarities €œ but the difference is in scale and manageability. If a spreadsheet is a 2-dimensional representation of a single data table, then think of a database as a multi-dimensional collection of related tables. For example, in a sales order system, customer information, customer contact history, and customer orders represent three separate tables linked together by a common identifier €œ the Customer ID. A programmed database can easily handle different functions related to these tables €œ finding a customer phone number, entering a customer order, reviewing phone calls to the customer. A spreadsheet would be good for handling one view at a time such as a list of phone numbers €œ but it would be very difficult to create and manage the multiple functions inherent in just those three tables in one spreadsheet. A programmed database also allows simultaneous access to the data by multiple users, while a spreadsheet only allows one user at a time to edit a file.
Spreadsheets and databases have very different strengths. A database is used to define, enter, maintain, and search data. Even a moderately powerful database can manage millions of rows of data spread across multiple tables. A spreadsheet’s strength is in analyzing data €œ once it has been defined, entered, and maintained!
But you might argue that a spreadsheet looks a lot like a database table. It’s made up of rows and columns of data €œ the same terminology we use to describe information in a database table (in addition to the more traditional records and fields). Excel is capable of sorting and filtering data, much like a simple database.
So where does the power of a spreadsheet fall away from an actual database system? Let’s create a simple donor spreadsheet and find out. We will make it simple to start, just a Name, single Address column, and donation for 2011 Spring Mailing:
|Name||Address||2011 Spring Fundraising Dinner|
|Nancy and Tom Terrific||100 Somewhere||$25.00|
|Ellen S. Markup||200 Whereitis||$50.00|
|Carol and Meryl Filligree||4 Upper Lane||$150.00|
Not bad for now. We can total the donations, use the spreadsheet to create a mail merge, sort alphabetically, or sort donations from highest to lowest. And now it’s the end of the year, so we can add another column for our year- end letter.
|Name||Address||2011 Spring Fundraising Dinner||2011 Spring Fundraising Check date||2011 Spring Fundraising Check No.||2011 End of Year letter||2011 End of Year Check Date||2011 End of Year Check#|
|Nancy and Tom Terrific||100 Somewhere||$25.00||3/15/2011||6656||$50||11/23/2011||8798|
|Ellen S. Markup||200 Whereitis||$50.00||3/21/2011||2321||$20||12/1/2011||78665|
|Carol and Meryl Filligree||4 Upper Lane||$150.00||3/23/2011||28767||$50||1/4/2012||764|
This is still not too bad. But what if we want to add many additional attributes €œ 5 more events, information about the donor such as referral, family or company information, donor ranking, etc. One could argue that you might end up with a huge spreadsheet with thousands of rows and dozens of column, but still usable. That’s possible unless you need to do more with the data. What if you want to give the spreadsheet to 10 board members to review contact information and rank the donors for an upcoming event. You can send out 10 copies of the spreadsheet, but how will you merge the data from 10 spreadsheets when they come back? What if you wanted to rank donors by year, not just event? Now you have to insert empty columns and perform sums between columns. What if you want to assign donors to board members and track the contacts made and resultant donations? It would be pretty difficult on a single spreadsheet. Another example – what if you needed to get a total of all donations made in August 2011, assuming the donations might have come from different campaigns (columns)? You would have to search dates across multiple columns and somehow total the amounts. Another example, how would you find all donors who have given more than a certain amount in the last 6 months?
Why is a database different? A database program is made to create relationships between different types of information. It’s also designed to separate repetitive information such as donations, from single instance data such as name and address. Different types of data are stored in different tables. For example, the Donor table only holds information about the donor €œ name, address, phone, email, etc.
To give flexibility to defining campaigns and events, we define campaigns, and campaign types.
With all the donations in a single place one can see that it would be easy to retrieve donation information filtered in a variety of ways. For example, the problem described above to find all Donations made in a certain month is suddenly trivial. Donations can be filtered and sorted by Donor, Campaign, amount, and many other attributes.
The screen below shows how easy it is to retrieve information from a well-designed database. Here, a search has been run for all donations greater than $200 from June through December 2011, shown in descending amounts.
It would take a lot of work to extract this information from a multi-column spreadsheet. With a true database, it just takes a few keystrokes.
To conclude, using a spreadsheet to manage your organization’s critical information is a severe limitation. Incorporating your data into a true database, either an off-the-shelf application, or custom system, will give you and your staff the flexibility to enter, manage, and analyze your data in a scalable, secure, multi-user framework.