What’s the difference between a database and a spreadsheet, and which should I choose?
We all have information which we would like to store, edit and analyse but where should we put it? Is it better in a spreadsheet or better in a database? What is the difference?
Let’s start with a simple example shall we?
A list of customers . . .
I need to keep track of my customers, so I need the company name and address, and the name of the main contact I deal with. In addition, the date I last I contacted them would be useful. Finally, I want to store the total amount of money they have spent with me. I want to be able to add up my total sales across all customers.
This is safely within spreadsheet territory. You can get can get started straight away, but wait! Do you only deal with the one contact at Super-Kart, or more than one? Better add some extra columns to my spreadsheet to handle multiple contacts, but how many?
The columns just keep increasing . . .
Maybe I should add columns for contact 1, 2 and 3 and see how I go, along with telephone 1,2 and 3 and email 1, 2 and 3. No, wait, better if I have just the 3 columns for name, telephone and email but repeat the company on more than one line.
Better still, I add another sheet and list the contacts on there with a reference number for each company. Now I want to list all the interactions I have had with each contact so I know who said what to who …. Uh Oh!
Use a database instead
This example illustrates a good point. The main reason for using a database instead of a spreadsheet is when you have a one-to-many relationship. In our case, it was the one-company to many-contacts relationship. The complexity of the type of information you are storing makes managing your information in a database a better fit. Do this, and you will see an improvement in performance.
There are other reasons for moving from a spreadsheet to a database:
Spreadsheets can only be accessed by one person at a time, so user numbers are a factor.
A large number of records causes problems with file size.
You cannot tune a spreadsheet to perform faster, so speed is an issue.
Plan ahead and avoid the pitfalls
So before you click that icon have a good think about where this journey might take you in terms of what you might need to store and how it might be used. Just a little up front planning can save your spreadsheet from becoming a victim of its own success.