Building a database application the DIY way
Business users experience the trials, tribulations and exultations of building a DIY DBMS, especially when IT expertise is not readily available or costs are too high.
When building, deploying and maintaining database management systems, a one-size-fits-all system typically can't handle every need and application. Using an array of software options -- homegrown, off-the-shelf and open source tools -- companies with varying degrees of in-house expertise can customize and adapt their systems organically to meet specific needs and perhaps save on costs.
"A DIY DBMS is what happens when you've outgrown Excel and don't have an IT person that can help," said Eddie Zaldivar, senior director of paid media and analytics at Inseev Interactive, an internet marketing service company in San Diego. Zaldivar was frustrated because he couldn't find a DBMS adaptable enough to handle his marketing team's insights. He decided to create a customer database since it was much cheaper than commercial system offerings and far more customizable. He took some courses with software maker Qwiklabs and was able to get his company's first prototype database up and running with Google Ads data in a few hours.
"I have been working on some form of marketing company for over 15 years," he recalled, "and over that time, we had only one database that actually worked." Those companies had IT departments that understood equipment needs or how to set up a basic database, he said, but when it came to building a database application specific to marketing, IT didn't understand marketing data and what was needed to generate tables and data architectures suited for the marketing team.
Pipelines and APIs
Among the biggest challenges facing Inseev was creating pipelines that could seamlessly combine into one database data from various internet keyword tracking platforms and their business applications. The SEO-focused company worked with several data engineers from a popular crowdsourcing service to set up the data pipelines.
"Initially, everything was fine," Zaldivar noted, "but every time the platforms would push out a new API update, our pipelines would go down." As a result, the database was offline for several days and as much as several weeks. Data pipelines aren't a set-it-and-forget-it proposition. They need constant monitoring, which includes preparing for changes to APIs.
To stay ahead of API changes, the company adopted data pipeline service Supermetrics, allowing Zaldivar's team to focus on marketing. Supermetrics also provided a schema that tracks a quality score for keywords over time.
Inseev also experimented with Google Search Console data that's offered by top tier data pipeline providers. But accuracy suffered when exploring different levels of granularity, such as search queries, page views and clicks.
Finding the right shoe size
In building a database management system, Inseev found that some data providers assumed that every enterprise used the same data structures. As a result, Inseev's data engineers would spend excess time setting up raw feeds to their database just to organize it properly.
In the case of Google Ads, for example, to generate a keyword statistics report, the data engineers had to combine several tables just to get the client name, campaign, ad group, keywords and stats. Supermetrics customized schemas so the data could be loaded into the database without combining separate tables.
Zaldivar believes Inseev's DIY DBMS provides the company with a competitive advantage by combining his team's marketing expertise with reliable data insights that help formulate more targeted and effective sales and marketing campaigns. "The quality of work and automation that we have created is pretty outstanding considering our size," he said.
Customization yields experimentation
One of the biggest attractions in building a database the do-it-yourself way is the flexibility to experiment with different ways of organizing data.
"A DIY DBMS means that the platform I use can be customized and is flexible to my needs," said Sharon Faust, founder of Quick Base Junkie, a consulting, development and training company that uses cloud-based database software. "Having the vast majority of my business information at my fingertips through the DIY DBMS has made me much more productive, keeping me focused on the activities that matter most." Some of those activities include creating reminders to follow up with consulting leads, determining which sales campaigns are most effective and how well she's managing cash flow.
"As my business has grown and evolved," she said, "I've added new apps, enhanced other apps and even archived some early apps." The result is a variety of apps built for CRM, project management, content management, lead tracking and finances.
Faust's experience customizing her own DBMS has also boosted her company's offerings by helping her clients customize their own database applications. "Many users of Quick Base," she explained, "have in-house developers who build and maintain the apps but reach out to someone like myself when their desires are more advanced or they simply don't have the time to execute."
DIY land mines
The DIY approach to building a database can be advantageous, depending on the application, company mindset and staff technical skills. But it also has pitfalls.
"In general, I'm pretty cynical about 'better Excel than Excel' approaches like this," cautioned Tony Baer, principal at database and analytics consultancy DbInsight. "You get what you pay for. These from-the-ground-up approaches will work only if your goals, data sources and [key performance indicators] are well defined, and, importantly, if you have the right talent. Otherwise, I'm a much stronger believer in cloud-managed analytics or data warehousing services than with low-code/no-code approaches for ingesting and integrating data and building self-service visualization."
Faust has encountered some issues and limitations with her DIY DBMS. Her company provides a relatively simple programming model for setting up data flows from one table or app to another through relationships or automations. But that can make the cost of customization more challenging. "Anytime you venture outside of the box," she explained, "there is a tradeoff between the enhancement and the expense and maintainability."
A large part of Faust's marketing is through social media. So far, she has not been able to include those metrics alongside her internal business metrics in an automated way. When building a database, another issue surrounding a DIY DBMS is the temptation to endlessly try new ideas. "At times," Faust noted, "I've had to really ask myself if a change or enhancement is going to yield the benefits or if it's just something I'd like to have or enjoy building."
To avoid some DIY land mines, Faust recommended backing up the DBMS before making drastic changes, documenting what apps have been built and how to use them, and keeping track of ongoing updates and changes. "As with any DIY system," Faust advised, "these practices can make a big difference when things go sideways. Yet I still find myself charging forward at times without them."
