Practical Data for Nonprofits: Part 3 — Why is it so hard to change something in my database?
You have invested in a data system. Maybe it’s ETO or Salesforce, or Apricot or one of hundreds of others. It was a big investment and it took a lot of your staff’s time and attention. But you can’t get any changes made, or they take forever, or the reports only show you half of what you need, and you’re starting to feel like you should just go back to Excel. What’s going on?
The technologies that run key parts of most operations — Quickbooks, Salesforce, Apricot, even that custom Filemaker database from an undisclosed number of years ago — are nothing at all like Excel or Google Docs. But because each type of system sits on a computer, it’s really common for folks to expect that one what one can do, the other can do. The tension is between the ways we use consumer tools like Excel that have a single purpose and aren’t tied to anything else, and the ways that systems which are tied to anything else must be designed.
An Introduction To The Stack
All computerized things are built on a “stack,” which is just layers of:
Hardware — your laptop or phone, or some big server farm somewhere
Data Storage — a file or set of files. Yes, even a powerful, globe-spanning database is just a set of files in the end.
Programming Logic — the code that provides instructions for how to store information and how to retrieve it, and how to transform information on input or output.
A User Interface (UI) — visuals and forms, places where you put stuff in and get stuff out. This often contains its own layers of data storage and programming logic.
YOU! Your interpretations, impressions, and processes as you interact with the system.
The most important thing to understand about the stack is that at each step, a set of human beings has made deliberate decisions based on interpretations and assumptions. Getting these pieces to coordinate in a way that produce an application like Apricot or Quickbooks means lots of decisions that were made between these pieces that glue them together in a specific way, which is often very difficult to change.
The UI is What You See, But Not All You Get
We are confronted with forms every day.
Even the “write an article” or “add a blog post” part of the platform I’m writing this on is actually just a fancy form. A form is method of asking questions in a repeatable manner.
These forms are a big part of your UI layer. From the stack above you can see that nothing stops with the UI — powering each form is programming logic; under each form is more programming logic. The UI alone, without these other pieces, may as well be a sheet of paper (which is its own data storage and retrieval system, and our brains are the logic to encode and decode it into meaning).
An illustration of how a form — a UI element — interacts with the underlying layers of the stack.
Multiple forms that connect together into a sequence of steps can be considered the start of an application. A form that uses programming logic to fill in some of the content from a database or file is also the start of an application. In an application, rules and a series of steps are applied to get predictable interactions.
“The Database”: an example of in-stack translations
In most places I have worked, people refer to the application — Apricot, Salesforce, Peoplesoft — as “the database” (among other, less polite things). In normal use, that’s totally fine. But for this article I do want to differentiate between that use and the technical term, which refers to a data storage system that most people never interact with directly. Our common use of “database” encompasses all layers of the stack, when the technical definition is only the data storage layer.
Relational databases are the workhorses of the database world. Most of them — like SQLServer, MySQL, and anything based on the SQL paradigm — store data in ways that are optimized for ultrafast retrieval, but relatively slow storage. There are many other types of database structures out there, and in the era of “big data” and low-code applications they are proliferating.
For the purposes of data literacy, it is not terribly important to understand the difference between SQLServer, Salesforce or Snowflake. They each serve a specific purpose and are optimized for it, and a good technologist will select the right tool for the job. It is, however, important to understand that all of these are back-of-the-house storage systems that must be interacted with using their own rules and specialized knowledge.
An example of how programming logic, database tables, and the user interface forms might combine to create a simple application, or, what’s really under the hood of “the database.”
Two Stacks Talking: APIs
“Why doesn’t ApplicationX integrate with ApplicationY? Application Z says it integrates with my database, but everything that comes in is broken and wrong! ApplicationN has an API but we still can’t get anything useful from it into our own systems.”
I have yet to meet a nonprofit that does not have some frustration around “integration” of technology. The ubiquity of stuff on our phones and laptops and TVs has led us to believe that we can have the same immediate interactions between larger, more complex systems, and it simply is not true.
Your financial system was designed — and has a ton of complexity in its stack to support— compliance with laws and norms, double-entry bookkeeping (with debits and credits), and detailed audit tracking. The data storage, programming, and user interfaces speak accounting. They manage information relevant to cash or accrual, and they do so based on rules that are coded into the data system.
Your CRM or donor management tool was designed — and also has a ton of complexity in its stack to support — your relationships with your constituents. The concepts that we often take for granted — like what is an address, or how do we track two individuals are in a family and what all does that mean to us — are encoded and stored just as rigorously as financial data.
To get your financial system to integrate with your CRM, even if they both have APIs or claim they “fully integrate”, it is down to your organization to define how they integrate. What do you want to happen when your financial system captures just the payor, but your CRM shows relationships? When do you book revenue from a pledge? For a successful integration, you must:
Translate between two stacks that were designed to do very, very different things
Translate in a way that is computer precise, not human precise. The computer requires unvarying rules for transforming data between one system and another.
A simplified illustration of how APIs typically allow two totally different platforms to exchange data.
Excel isn’t a Database (sorry)
But it is most people’s mental model for “how technology works” when it comes to business applications, which is why I’m writing about it here.
You may be surprised to learn that Word and Excel are much more like a piece of paper than a database application. You can write whatever you want into any part of Word, Excel, or a piece of paper. They are their own self-contained storage and retrieval system. Our own brains provide the “programming logic” to make sense of what is there, without the forced (and artificial-feeling) precision demanded by other computerized systems.
Many people assume that if they can just drop a formula into Excel to do what they want, other systems must work more or less the same, right? The critical distinction between a database-backed application and Excel is that there are translations between multiple parts of the stack in an application but not in Excel. It’s challenging for two people who speak different languages to reach an understanding about a complex legal or technical matter through a translator. It is similarly challenging to have different parts of the stack “understand” each other in support of a common goal.
So why isn’t everything just done on the faster, more flexible systems like paper or spreadsheets? Simply put, the more reliable outputs that are needed (whether in reports, or products, or information) or the more people performing the input, the more rules must be established, communicated, and enforced. And as anyone who has tried to create a funder report off of three or four spreadsheets knows, it is simply impossible to support scale or efficiency of any sort with these tools.
OK, so why does everything take so long?
The unavoidable rule is: the more complex the system, the more that’s involved in the change.
An important corollary to this rule is that in structured systems (like database-backed applications, which are complex), flexibility and performance are at odds with one another. The more flexible you demand something digital must be, the less you can do with it in a structured way without paying a huge cost in time or money (or both).
Consider indoor plumbing. Most of us reading this can turn on a tap and have clean (or clean-ish) water, immediately. To obtain that, a vast infrastructure has to be in place that stores the water, purifies it, pumps it into the pipes and pushes it throughout the town. The faucet in your home has to connect to your home’s pipes which have to connect to the infrastructure. The only way for that to happen relatively efficiently and well is to have standards that everyone conforms to, at least where the critical connections are made.
Changing the shape of your faucet might be a matter of a few hours work, but adding a new home or apartment building means ensuring the infrastructure has capacity to accommodate it, then connecting it up new structures to the old (which may involve shutting down water to your whole neighborhood for a bit). Moving from the massive set of rules and experience and structure that was designed to support indoor plumbing using the above paradigm to ensuring the entire city has a more eco-friendly greywater system would be a years-long undertaking. Not because the physical equipment is hard to manufacture, but because it has to be put into a complex system.
Any change to infrastructure is a significant undertaking because you have to understand what is already there, understand why it is already there, understand what you risk by disrupting it, plan for the disruption, design the new system, build the new system, and integrate the new system into the existing system.
Data systems are infrastructure. The more complex and powerful your data system is, the harder it will be to change it.
Before you throw away your current application…
All of those concepts about what your financial system and CRM were designed for, and how hard it is to change a system, is also the key to why so many agencies cycle through so many CRMs and donor management tools.
Financial management — bookkeeping and accounting — is a highly artificial and inherently rules-based way of thinking about things. Systems built to assist with these processes are usually less contentious than CRM tools because we come into them with a shared ability to reference written rules about how money should be recorded. The humans involved have learned the same rules they expect to see in their software, and more important, have largely accepted that there are rules that they have to memorize and follow.
CRM and donor management software was built on a set of assumptions too, but these assumptions are about how humans do human things, like live together, work together, and communicate. These human things actually have tremendous variation and nuance that is difficult for rigid, rules-based systems (like computers). At the same time, because they are human things, humans often reject the artificially-structured approach that is demanded to properly learn these systems.
Put another way, we didn’t go to school to learn how addresses and households work, and we don’t hire specialists who are trained in any sort of compliance around addresses and household data management. We just sort of absorb what’s in our environment, assume we know how it works, and even if we evolve our understanding to accommodate many different cultures and paradigms, we still don’t perceive it as a rules-based thing to require step-by-step learning or enforcement. But in a computerized system, that is exactly what it must be.
No matter what system you’re using, if you are annoyed by it or think it’s slow, my advice is always to learn what its underlying assumptions are first and what the capabilities are within the system’s rules before throwing it away. Switching costs are often high and you’re not going to find a perfect digital system, so understanding these dynamics can save you money. Once you’ve explored the assumptions you have against the system’s assumptions, if you still hate it THEN it’s time to switch.
This article is part of a series on data literacy for nonprofit leaders. Its goal is to share terms and concepts that aid in making good technology decisions when you’re not a technology expert (or even if you’re a little bit tech-phobic).
See the overview here
Next up: Data is meaningless (without context)