This article explains what a database is and why life is much better with databases than only using spreadsheets. Knowing more will enable you to make better choices about how and whether to use a database vs spreadsheets and whether to invest in a database.
The dictionary definition of “database” is a large collection of data organized for rapid search and retrieval. It's accurate, but pretty vague!
To get a more practical understanding of a database, let’s start with a very simple data storage need using a spreadsheet. Then, as the spreadsheet becomes more complex, we'll see its limitations become clear. Lastly, I'll explain how a database overcomes these limitations of spreadsheets.
Why Not Just Have One Big Spreadsheet?
Let’s say we have 100 clients and 5 staff who work with them.
And let’s say that for each client we only need to track basic information – (1) First Name, (2) Last Name, (3) Birthdate, (4) Email, (5) Phone, and (6) Mailing Address. The first few rows would look like this (I won’t be including all the clients for simplicity's sake):
So far so good! If this is all we need to track, we don’t need a database.
But let’s say we also want to track each client’s household members (and some basic info on each household member, like their relationship to the client, their birthdate, and contact info).
Even this relatively simple data storage need highlights some very real obstacles for using spreadsheets to track this kind of data.
Tricky Obstacle #1 - Lots of Columns
Let’s say each of these clients have 5 household members and for each of those household members, we want to capture basic pieces of info: (1) First Name, (2) Last Name, (3) Birthdate, (4) Email, (5) Phone, and (6) Relationship to Client (e.g. “Mother”, “Sibling”, “Friend”, “Child”).
Hm... All of a sudden, we now have to add 6 columns for each of the 5 household members. That's 30 columns in total. This gets unmanageable pretty fast. This is the problem with the One Big Spreadsheet method. They just get too big to easily use.
(You won't be able to actually read the text below, because it's not important. I’m including it to illustrate the SIZE of the spreadsheet we’re talking about.)
30 columns is a lot, but in my work, I've seen spreadsheets with more than 100 columns. This is very, very hard to manage!
Tricky Obstacle #2 - Variable Number of Household Members
But let’s be more realistic. Most clients aren’t going to have 5 household members, though maybe only one or two will. So let’s say that most clients only have one or two household members and some have none.
But we still need to have all those 30 columns for the one client that DOES have 5 household members! Because if we only included columns for two household members, then where would we put the information for that one client’s third, fourth, and fifth household member? That data would be lost without having all 30 columns.
So not only do we now have a very cumbersome spreadsheet, but we also have one that’s mostly empty.
Tricky Obstacle #3 - Reporting Challenges
To give credit to our One Big Spreadsheet solution above, we HAVE captured all the necessary data. But it’s being stored in a cumbersome way that makes it hard to find what we're looking for.
And there’s another obstacle that comes from setting up our data this way, and we’ll run into it if we want to run reports on our data.
Multiple of the Same Column Means More Problems
For example, Let’s say we wanted to run a report to get a list of all the household members whose birthdates fall between Jan 1, 1970 and June 1, 1990 (maybe there’s some age-based resource they can access). But we can’t sort the spreadsheet by household member birthdate because those are in 5 different columns (highlighted below).
To summarize our data, we'd have to do a bunch of copying and pasting into a new spreadsheet, which is annoying, time-consuming, and prone to error.
Summarizing and Reporting with One Big Spreadsheet
Now let’s say we need to produce 10 different reports every month as part of reporting requirements. We need reports based on household members’ birthdates as well as household members relationships to the client.
All of a sudden, we’re copying and pasting 10 times each month into 10 different spreadsheets. By the end of the year we have 120 of these spreadsheets. Now we’re running into even more problems. It's hard track when the spreadsheets were last updated. Staff get confused about which spreadsheet to update. We only have current information once a month (at best).
Yikes! Just tracking household members reveals major limitations to using spreadsheets.
Why Not Use Multiple Spreadsheets Instead of One Big One?
So you may be thinking: “We can just create a separate spreadsheet with just Household Members”.
Hey! That’s not a bad idea! This definitely fixes some problems. Let’s see what this separate household member spreadsheet looks like:
This Separate Spreadsheet solution solves the three “tricky obstacles” we came across above:
• (1) It has a manageable number of columns.
• (2) It allows for a variable number of household members (between 0 and infinite).
• (3) We can run our 10 reports without a ton of copying and pasting (since there aren’t multiple birthdate columns).
We’re back in business with our two spreadsheets! We can look at either our client spreadsheet or household member spreadsheet. And if all we are tracking is this basic info, our two spreadsheets could be fine.
But realistically, you'll many more than two things to track.
What if we also want to track (1) previous addresses, (2) employment history, (3) client goals, and (4) client outcomes, etc? If we want to get a comprehensive view of a client, we now have to have 6 separate spreadsheets (these four new ones, plus the client and household member ones). If you want to see all of it at once, you somehow need to have 6 spreadsheets open. And for each, you'll need to scroll past all the data on other clients to find what's relevant to the client we're interested in.
And additionally for the Multiple Spreadsheet Method, we have to keep adding more spreadsheets for each new type of data we’re tracking.
If we had 24 different types of client data to track, that would mean having 24 different spreadsheets. This starts to get painful for both entering new data as well as retrieving old data.
Challenges with Summarizing and Reporting with Multiple Spreadsheets
So we've found that multiple spreadsheets can also be unmanageable, but at least they make reporting a cinch, right? Well, unfortunately, they also have big limitations in this area (that only a database is going to solve).
Let’s say staff needs to track how long they spend with clients and some basic notes on what they did. In short, we need a time log spreadsheet.
And let’s say that for reporting and payroll purposes, that at the end of each MONTH and each YEAR we need to know:
• (1a) how many hours were spent on EACH client
• (1b) how many hours were spent on ALL clients IN TOTAL
• (2a) how many hours EACH staff member spent on client work
• (2b) how many hours ALL staff member spent on client work IN TOTAL
Using spreadsheets, we’re faced with a lot of bad options to generate these reports.
We almost definitely don’t want a single spreadsheet as a time log that includes info on ALL clients that ALL staff members would be updating. There are privacy issues for both clients and staff. It would be hard to get a sense of the client’s history since all clients would be mixed up together. It could lead to staff accidentally editing or deleting other staff’s data.
So what what else can we do?
Bad Option #1: Should we make one spreadsheet for each of our 100 clients?
We could make 100 new time log spreadsheets, one each of our 100 clients. Remember that we have 5 staff serving around 20 clients each, but occasionally they’ll work with each other’s clients. These client time logs might look like this (client name is highlighted in green):
How does this system do for our monthly reporting needs?
✓ (1a) how many hours were spent on EACH client
✘ (1b) how many hours were spent on ALL clients IN TOTAL
✘ (2a) how many hours EACH staff member spent on client work
✘ (2b) how many hours ALL staff member spent on client work IN TOTAL
To make the last three reports work, we’re going to end up copying and pasting the 100 client spreadsheets together (and then do filtering). This is going to be time-consuming, tedious, error-prone, and demoralizing.
Bad Option #2: Should we make one spreadsheet for each of our 5 staff?
Alternatively, we could make 5 time log spreadsheets for each of the 5 staff people. These spreadsheets would include all entries that staff person made. Those spreadsheets might look like this (staff name highlighted in green).
So let’s see how this system stacks up with regards to reporting.
✘ (1a) how many hours were spent on EACH client
✓ (1b) how many hours were spent on ALL clients IN TOTAL
✓ (2a) how many hours EACH staff member spent on client work
✓ (2b) how many hours ALL staff member spent on client work IN TOTAL
Three out of four reporting objectives is better than one out of four! We’ve achieved this because it’s much easier to copy and paste 5 spreadsheets together each month than 100. There will still be calculations and filtering to get data on the 100 clients and that’s going to be error prone, but it's much better than Bad Option #1.
The major problem with this Bad Option #2 is that it’s built for administrative work, not for the staff actually working with clients.
Staff want to see each client’s data in one place so they can see what they’ve done previously and where they’ve left off. They don’t want to navigate a spreadsheet of notes written for 20+ different clients.
And additionally, what happens if Erin works with Sally’s client because Sally was sick that day? Erin’s notes for that client won’t be accessible to Sally, or at least not anywhere easy to find, since it will be on Erin's spreadsheet since Sally was sick.
Spreadsheets create major headaches likes these all the time.
The End of the Year Nightmare
And with this same example, let's not forget we need to do all of this for EVERYTHING at the end of the year!
No matter how you slice it, it’s going to be a lot more copying, pasting, and manual excel calculations. It will be time-consuming, tedious, error-prone, and demoralizing.
A Database to the Rescue
Luckily, a well structured database solves ALL of these problems.
Generating these reports will no longer take hours or days. They’ll take a few seconds.
Basic info in Salesforce
Let's translate our spreadsheets into a Salesforce database. Here’s what our basic client info looks like now. So far it looks pretty similar.
Viewing a client in Salesforce
BUT from the above list, if we click on a client's name (in blue) here’s what we see.
We get a view that’s much more user friendly than a row on a spreadsheet. And we can immediately see a few bells and whistles: the embedded google map and automatically calculated “Age” field (based on the birthdate). It will also show you who the “owner” of the record is, which can be very helpful.
Households in Salesforce
You might also notice at the top of the client record it says “Account Name”
If you click the link under Account Name ("Anderson Household" in this case), it brings you to the Household
And voila! It shows all our primary client’s household members (in addition to the client)! If you click on a household member, you can see more details in a user-friendly layout for them. Here's an example household member below.
In just a few clicks we can easily find our client and then see the household members related to that client with all their basic info.
All the client’s data right where you want it
But we're just getting started. Back on the client, if we click on the “Related” tab, we can also see the other key pieces of information for that client all in the same place: (1) previous addresses, (2) employment history, (3) client goals, and (4) client outcomes.
They’re all just there! You can add or view them directly from the client. No more searching through 10 different spreadsheets to find the info you need.
Reports
And remember, a major limitation of spreadsheets is reporting deficiencies. With a database, you only have to set up a report once, and then the current data will be there any time you want to view it. Here's an example of a report showing all household members aged 0-18. It took three clicks to view this data. And it will always be current up to the very moment I view it.
Or if I want to see the breakdown of different types of relationships that household members have with clients, I can create the report and then add a graph. Again, the most current data is just a few mouse clicks away.
Now let's take a look are more complex reports. Here's both a graph and data table that summarizes thousands of Time Log entries to create up-to-date information formatted for staff's hours in total for the year as well as broken down by month
And a similar breakdown for each client, also by month
And if you want to view a bunch of reports all at the same time, you can put them all on a dashboard – so instead of opening multiple reports, you can just open a single dashboard and view it all at once (you can also click into the individual reports if you'd like).
So we've overcome the limitations of spreadsheets by using a database. But there are two more very important features to think about that make databases a far better option than spreadsheets.
File Storage – Dragging and Dropping
Spreadsheets have big issues around storing and retrieving files – that can't do either.
With Salesforce, we can simply drag and drop files. I dragged and dropped the files you see in the screenshot below from my desktop to Salesforce.
If you’re using spreadsheets but still want to store client files, then you’re also using a completely separate file storage system. You might be using paper files or electronic storage with something like Google Drive, Microsoft SharePoint + OneDrive, etc. But the spreadsheets and file storage won't be linked, which is a huge flaw. A database like Salesforce makes file storage as easy as dragging and dropping and makes retrieving the files equally easy by going to the contact in question and seeing ALL their related data in one place.
Tracking and Documenting Email
Spreadsheets are also not great at logging communication (email messages, text messages, etc). We could copy and paste every single message we send and receive about a client, but that’s very time consuming and tedious. With a database like Salesforce, we can integrate it into our email client (Gmail or Outlook) so that we can associate emails we send and receive with clients with great ease.
The screenshot shows how tracked emails look on a client's page.
And if you click on an email, you can see the contents, the email "from" and "to" email addresses, and attachments (if the email has any).
A Salesforce database offers so much more
We covered the limitations of spreadsheets and how a Salesforce database solves these problems. AND we've only just barely scratched the surface of what a database can do. To get a free consultation with your database needs, please book a free call using the button below!