Reasonable planning: How to choose the right database for your app?

This article is reproduced from the public account "Reading Core Technique" (ID: AI_Discovery)

It’s always exciting to start a new project—you have the freedom to design and build it the way you want. But if you don’t plan it well, it will cause trouble in the future. One of the most critical decisions you need to make is choosing an app database, and the purpose of this article is to introduce the database options—listing their advantages and disadvantages to help you choose a database wisely.

[[390048]]

Key-value

The database is structured like a JSON object, each key is unique and each key points to a certain value.

It keeps data in memory and runs fast, but has limited capacity, so it is difficult to store large amounts of data. Since there is no hard disk used, it runs very fast. Since there are no queries or joins involved, there is no need to worry about data modeling. Since there is no schema, developers always have the flexibility to change the data to their liking.

Conditions of use:

• This technique is mainly used as a caching mechanism when certain parts of the data are frequently retrieved and observed.
• Therefore, key-value technology is commonly used in conjunction with other databases as a caching mechanism.

Wide column

Wide columns are like key-values ​​on steroids. The value is modified to store a set of columns instead of just plain data.

With the introduction of a set of columns, it is now possible to group related data, but there is still no standard schema. Therefore, each key can point to a different group of data.

Since it has no schema, it can handle unstructured data and comes with a query language called CQL, which is similar to SQL but much less powerful.Data comes in continuous streams, such as from IoT devices, stock markets, financial transactions, or Netflix viewing history.

Conditions of use:

• Write frequency
• Not updated or read frequently

It is still not universal. Therefore, it can be used to store historical data from all different applications.

Document database

It is one of the most popular database technologies we use. Obviously, it consists of documents, and each document is a set of key-value pairs. They are unstructured and do not require a schema.

Documents are grouped into collections, which can be structured into logical hierarchies. Logical data collections group related data in a more logical way, which seems similar to relational databases.

The database cannot run join queries, how to get all related data at once? Store them all. Encourage denormalized databases, be prepared for data duplication/inconsistency.

Reading data is very fast, but writing and updating data while maintaining consistency is a challenge. Document databases are great for general-purpose applications and probably a good fit for most apps, games, and IoT.

If you don't know much about the database schema, logging the database is the best way to start.

Popular document databases

When the amount of data is large and there are direct or indirect relationships between the data, document-based databases cannot accommodate it. In this case, you must run multiple complex queries and then merge all the received data in the front-end application, or you can use a relational database where these complex queries are managed by the database.

Relational Database

Some of the most popular databases in this category include MySQL, Postgres, and SQL Server. They have been around for a long time and are good choices for many applications.

Imagine a car factory that has different hubs to produce car parts. Suppose the doors are made in one place, while the wheels, body, and interior parts are all made in their own separate places.

Hypothetical car factory blueprint

Each manufactured part has a unique ID. So once the car is assembled, all the parts can be taken from different places and the car can be assembled.

Building such a factory requires a blueprint to be drawn up to ensure that the entire production process is very efficient and optimized. This blueprint, when used in a database, is called a schema. Therefore, a schema needs to be planned for the database to ensure that the database is also efficient and can meet the data needs of the application.

shortcoming:

• Changing the layout of a car plant, like changing requirements, can cost the car company time and money over time. Large applications face a similar situation. Make sure you use a relational database when the requirements are clear.
• Also, once you build a factory that can produce 30 cars a month, it’s hard to scale up to 90. Similarly, relational databases are hard to scale, but there are exceptions, such as Cockroach DB and PostgreSQL, that were designed with scaling capabilities in mind.

advantage:

• SQL databases are ACID compliant, meaning that even though read and write operations may fail between them, data validity and integrity are not compromised - making it ideal for banking/finance related data.
• Once you have a schema in place, you can ensure that the stored data will always be stored in a fixed structure following a set of validations defined in the schema.

Which is best for you?

• If the requirements are clear and do not need to be changed drastically, choose this.
• If you are not sure about your requirements and are in some experimental stage, it is best to use a NoSQL database.

If there is no need to create a schema and the relationship is stored directly as data, which one should I use?

Graph Database

Data is stored in nodes and relationships are defined as edges. Let's see how to do it.

To find all students studying computer science in a SQL database, you need a lookup/intermediary table that stores a separate record of all students studying computer science.

Graphs are simpler and clearer because the relational parts of the data do not have to be stored separately, but are automatically included with the new data.

Relationships are easier to record and maintain in diagrams

With this new way of directly displaying the relationship between two nodes, complex join queries become simpler, greatly improving the performance of the database compared to SQL. Therefore, this database can be used when the performance of the database is reduced by relying on a large number of join operations.

Searchable database

If you are building an application like Google where you have to quickly return all matching records for small string query searches, then this is a full text search engine. These databases are based on the Apache Lucene project which started in 1999.

Algolia and MeiliSearch are full-text search engines. They look similar to document-type databases. There is an index, and data objects are added to it. The search database engine will analyze all the text in the document and create something called an inverted index.

When you query for something, the database only checks the inverted index, which makes the whole process seem incredibly fast, even for large databases.

Multi-model database

There are many databases to choose from, but the most popular seems to be Fauna. As application developers, we usually only care about JSON, which we can use in the front end of the application. With Fauna, you don't have to worry about data modeling, schemas, scaling, replication, or normalization processes, just get JSON data. Use GraphQL to define how to access the data.

Let's take a scenario of an Instagram-like application. We will use JSON to define rules for users, posts, and queries.

We just uploaded the GraphQL schema, which automatically created a collection to store the data and an index to query the data. Behind the scenes, it will consider how to utilize different paradigms such as relations, graphs, and documents based on the GraphQL schema you provided. Simply add data in the same way you would in a document database, without the limitations of data modeling.

advantage:

• It is ACID compliant and extremely fast.
• Don’t worry about infrastructure. Just define your data needs and the cloud will take care of the rest.

shortcoming:

• The price is an obvious downside. Good things aren't free, but they do offer generous plans/open source options for developers who want to learn, as well as small startups.

Here are some of the most important features of Fauna worth noting:

That’s not all! There are many different databases to learn about, but I hope this introduction will help you have some clarity and direction when designing your applications.