How to choose the right database for an app
Lucid Content
Reading time: about 11 min
Topics:
Considering how important it is to have effective, high-quality data storage and processing, your database shouldn’t be an afterthought. The right database can help ensure that your app becomes or continues to be a success with users, providing the right user experience and being easy enough to maintain that you don’t risk losing valuable resources or data.
Read through our tips on how to choose a database—but first, let’s dive into why this choice matters.
How database selection impacts your company
With all the work you put into perfecting your app, creating the right interface, and writing easy-to-maintain code, the last thing you’d want to do is hastily choose a database. You will need a really specific understanding of your app’s information needs in order to make the right database choices. A good fit supports your app and the hard work you put into it, but a bad database might only hurt your efforts.
Don’t skip this step or just choose anything—in another 18 months, you could wind up revisiting these decisions with even more backtracking than before to make the information usable.
Potential upside of choosing the right database
- Having the flexibility to scale properly: With the right planning and choices now, your database is able to scale and adjust to your needs now and in the near future.
- Improving your UX and pleasing your customers
- Maintaining strong availability and uptime: A database that’s a great fit for the specs you need will be much more likely to perform to your and your users’ expectations. Response time, availability, and the number of simultaneous users and requests supported can vary.
- Saving time with a database that’s “easy to forget about”: If your database is a close match to your needs, then you can probably save time now while reducing your maintenance efforts later. A database you’re stuck spending extra time on is likely not doing you any favors.
Disadvantages of having the wrong database
- Your data isn’t a good fit for the database: Choosing a database that isn’t optimal for your data can lead to higher costs and reduced performance.
- Support and operations costs break your budget: If you don’t plan costs or scope your database appropriately, you may find your organization devoting too much in resources later.
- Security challenges don’t fit your expectations: For instance, non-relational databases are generally not as secure as relational databases. NoSQL databases will generally require additional security precautions.
- Downtime incidents increase: A database strained by your data, number of users, and processing demands may slow down or experience downtime. Also, some database types may not be a good fit if you need high availability and reliability.
- Your user base quickly outgrows your database: If your needs are expected to change quickly, you may find yourself needing a new database strategy in a year or two. This is not necessarily a problem but is definitely something to think about. Anticipating your upcoming data needs, think about what your organization will have to do to quickly add a database or scale as usage increases.
Factors to consider when choosing a database
There are many factors that are worth considering when you’re getting started with choosing a database, such as deciding between a relational (SQL) or non-relational (NoSQL) database. Today, you can also choose a database that brings the best of both worlds together in a NewSQL database.
Essentially, it all comes down to the data your app uses, how your app uses it, what your customers’ expectations are, and what your strategy is for keeping these customers happy.
General questions
- Timing: Why are you making a database decision right now? What lifecycle stage is your application in today?
- Existing database(s): If you’re already using one or more databases, what is the impetus behind your choice to find a new database solution? Why is your database not fulfilling your expectations?
- Planning and forecasting: Have you considered your upcoming database needs?
Database size
- Current needs: How much data does this database need to store once it goes live?
- Future needs: Will your database use over 1 TB? How long will it take to exceed this amount of data?
Data type
- Unstructured data: Is your data unstructured? Unorganized information lacking context that would make the data meaningful on its own. An example of unstructured data might be three emails’ worth of text from your inbox.
- Structured data: Is your data structured? Data along with contextual information. For instance, structured data could be the names of meeting participants, the dates and times you plan to meet them, and the topics you’re meeting about. The database shows the relationships—your meeting is specifically with Juan at 3:00 p.m. on Tuesday, and it’s about that project you’re managing.
Just as there are benefits to structuring your data, there are also use cases for unstructured databases. If, for instance, you need a database to hold social media data, then you’re working with unstructured or semi-structured data and you need a database that can efficiently do so.
Data retrieval
- Data complexity: Does your database need to be capable of complex data management and retrieval?
- Data structure type: What type of data structure will you use? Depending on your data structure, you may have a harder or easier time querying your data and generating reports.
- Reporting functionality: Will your database be reporting a variety of data in response to user or application needs?
Future needs
- Scaling priorities: Generally, is it more important to you that your database be able to scale quickly to greater workloads, or is performance more important?
- Scalability to use levels: Is your business adding demand to the database rapidly? Do you need the flexibility for your database to scale back down outside of peak use periods?
- Scaling data sizes: Will you increase data beyond 1 TB, requiring sharding to split the database into smaller pieces? Above 1 TB, sharding becomes more difficult with older data.
- Geographic locations: Where are your users located in the world? Do their locations pose any challenges for your app or your database?
Code considerations
- Programming languages: What are the preferred languages you’ll use with your database?
- Reads and writes: What’s the ratio of reads and writes you think will occur with the database? What will you need to do to ensure data consistency and integrity in these processes?
Sharding and scaling process
- Horizontal scaling: If you’re growing your data capabilities from a database you already have, are you adding a database and splitting your data into multiple databases? Do you anticipate a need to scale to a new database in the near future?
- Vertical scaling: Would vertical scaling be suitable for your data if you need to grow, or would vertically partitioning data sets adversely impact the integrity of your data or the functionality of your database?
By reviewing these questions, you can develop your database mindset and prepare to start making database decisions as an organization.
Steps to finding the right database for your app
As you consider your database needs, you may find it helpful to follow a specific process.
1. Analyze your project’s needs carefully
At this first stage, you will want to loop in all your stakeholders, including your development team, to start defining your database must-haves and expectations.
2. Review your tech stack
Based on your existing technology stack, determine what tech requirements apply to your database. Thoroughly understand the data structure(s) you need. For instance, any other databases your new database must work with, any integrations you use, programming languages, cloud architecture, and any other requirements to consider depending on your app.
3. Consider your business needs
From a business planning perspective, look at what other stakeholders in your organization will need from the database and how your new database might impact other business operations.
4. Model and visualize your data
Create a model to test the theories you’re developing. Determine the type of database that fits your data and your application before moving on to the next step. Visuals such as data flow diagrams and entity-relationship diagrams (ERDs) can help you capture how your application uses data.
5. Create a plan
Choose a database solution and begin drafting timelines, project teams, and champions to help with the transition. What this plan looks like will largely depend on whether you’re adopting a new database from scratch, adding databases, or transitioning data and preparing for a migration. Older data may be more difficult to share if you need to scale horizontally, for example, and your engineering team may need more time in order to protect the data’s integrity during the process.
6. Begin implementation
With your homework done, you are ready to start the implementation process for your database. Through the process, use models and visualization to help your team stay on track.
Popular databases
Different databases are available to fit your organization’s application needs, but the decision is much more than just whether you want a SQL or NoSQL system—with NewSQL, you can even balance some of the pluses and minuses of the two.
Carefully consider these options.
SQL databases
As relational databases, SQL systems are great for highly-organized data where you need to store the context and relationships that provide meaning to your data. SQL databases use Structured Query Language (SQL) to facilitate your interaction with the database.
Advantages
- Security: Create access permissions and reduce the likelihood of your database falling prey to data theft.
- ACID compliance: Relational database management systems (RDBMS) must be ACID compliant, meaning they apply atomicity, consistency, isolation, and durability. This reduces your risk of a database failure or critical error harming your data or your users. Even if a specific activity fails, your database will still save data and isolate that transaction from harming other transactions.
Disadvantages
- Not ideal for unstructured data: For large workloads of unstructured or semi-structured data used in IoT or data analytics, you may find a SQL database less effective than a NoSQL option.
- One server limit: You may need to invest significantly in hardware if you’re using a SQL database for a large amount of data since relational databases work on only one server.
Examples of SQL databases
- PostgreSQL
- MySQL
NoSQL databases
For unstructured data, a NoSQL database is a better option. These databases are easier to scale and generally much cheaper to run than SQL databases, too.
Advantages
- Low cost: Because you aren’t limited to one server with NoSQL databases, you can run your database across multiple servers and take advantage of the savings on computing resources. You don’t necessarily need to pay more for hardware that way.
- Ability to scale quickly: NoSQL scales efficiently because you can use the cloud and have much more flexibility if you need to grow.
- Reduced downtime risk: Arguably, with less reliance on a single server, NoSQL provides better reliability than SQL databases do.
Disadvantages
- New technology: As the new kid on the block, NoSQL databases are not as mature and established.
- No ACID compliance: These databases aren’t ACID compliant and may be more challenging from a security standpoint.
Examples of NoSQL databases
- Key-value stores: Amazon DynamoDB is one example of a key-value database, connecting one value to another and storing these as pairs.
- Columnar database: Arrays of values are stored for retrieval as a column. One example is Scylla.
- Graph: These databases store data as individual graphs that visually show information relationships (for instance, Datastax Enterprise).
- Document-oriented database: With a document-oriented database, individual documents such as XML files store information along with the relationships. MongoDB is one example.
NewSQL databases
There is also a third type that combines SQL and NoSQL features. NewSQL is more scalable and flexible than SQL while having ACID-compliance. Right now, adoption is relatively limited because NewSQL doesn’t have the full library of SQL tools quite yet.
Given NewSQL’s recent creation, many NewSQL database solutions are still in development, but they could be promising for cloud-based applications in the future.
Examples of NewSQL databases
With how much you need to consider, it’s worthwhile to craft your plan carefully and make sure your new database supports your app needs now and, ideally, eases your scaling in the future.
Once you've got a database selected, learn how to document it using entity-relationship diagrams.
Read moreAbout Lucidchart
Lucidchart, a cloud-based intelligent diagramming application, is a core component of Lucid Software's Visual Collaboration Suite. This intuitive, cloud-based solution empowers teams to collaborate in real-time to build flowcharts, mockups, UML diagrams, customer journey maps, and more. Lucidchart propels teams forward to build the future faster. Lucid is proud to serve top businesses around the world, including customers such as Google, GE, and NBC Universal, and 99% of the Fortune 500. Lucid partners with industry leaders, including Google, Atlassian, and Microsoft. Since its founding, Lucid has received numerous awards for its products, business, and workplace culture. For more information, visit lucidchart.com.