Database principles and evolution, with some examples

Come on, it's that time of year again, and like all developers you've had a thousand new ideas during your vacations (but don't pretend you haven't). So let's talk about the most crucial point when you're at the idea stage and about to design your incredible mega-start-up project: the database.

Almost everything you use on the Internet today is linked to a database. As its name suggests, its role is to store a wide variety of data in order to carry out the actions of a website or application.

You've all logged in somewhere before, and for your login to work, your username and password (not in clear text!) have to be stored somewhere. So that you can be "remembered".

The database is the memory of a site or application. Without it, there would be little or no trace of past activity.

"Oh yeah, it's a really important organ, isn't it?

Yes, and that's why when you're thinking about a new project or an upgrade, it's generally the first thing you look at.

There are several types of base and several ways of creating them. Like programming languages, it all depends on the needs and evolution of the project.

Relational databases

Since this is the market leader, I'll talk about it first. This is a relational database system (DBMS), meaning that it works with relationships between data types.

You can think of it in terms of tables that connect to each other. For example, a table of users with all their information and a table of articles with all their content. Since each article was written by a user, there's a relationship between the two tables.

"Okay, great, but what's the point of writing the relationships in the database? We could just as easily have articles and users each on their own, couldn't we?"

In reality, yes, it's possible, but if you arrive at this result when you design your project, there's a problem somewhere. How will you ever find out who wrote what? Or conversely, what was written by whom? And yes, because every relationship has a double meaning that doesn't give the same result.

To cut a long story short: a user can write lots of articles, whereas an article will a priori have only one author. If you save everything separately and list the articles, then you'll only have their own author, probably duplicates etc., and it will be impossible to easily find out, for example, all the articles a user has written.

Database design is therefore very important in a project not only for basic operation but also for future performance and nowadays to extract valuable data easily.

Mysql

The market leader for many years, Mysql is the benchmark database system in the world of web development. It uses the famous relational database principle just mentioned, and operates in a client-server fashion.

As its name suggests, it uses the SQL language to interpret queries that extract the required data from the database.

To find out more, their official site covers a number of in-depth topics:

Why MySQL?

MariaDB

MariaDB is a fork of Mysql, i.e., its entire operation is based on MySQL, and this system takes over all its fundamental principles. (Fork comes from the Git vocabulary and means to create a branch on which to develop an additional layer of code).

The main advantage of MariaDB is undoubtedly its performance, which exceeds that of MySQL, which seems quite logical given that it was created for more modern projects. In fact, there's relatively substantial caching, as well as more efficient indexing. Its open-source nature also makes it an attractive choice for some developers.

PostgreSQL

Without doubt the most robust and scalable web database tool. In fact, Postgre can be used in several languages, can contain a multitude of data types, and can store functions and other scripts directly implemented by users. So yes, you can touch the code and inner workings of the DBMS directly. All this is coupled with a highly ergonomic backup facility and the ability to store large volumes (image, video files, etc.).

So it's hardly surprising to find it just behind Mysql on the market. Its competitor has a long and widespread history that makes it the market leader, but Postgre attracts many new developers and projects for all its undeniable assets.

As we've already mentioned here, Mysql isn't made for all uses? :

So much for the main DBMS (database management systems) you're likely to be seeing in the next few years. But in terms of conclusions, it's hard to say which of them is better than another. In fact, it's a conclusion we often come to in our field, simply because tools are developed to handle certain types of case.

NoSQL databases

Unlike the systems listed above, NoSQL databases don't use SQL (structured query language) to retrieve data, but rather "document" data.
Having arrived with the NodeJS (JavaScript) movement, they are often found today, and their use is very confusing for a developer who is used to databases using SQL.

MongoDB

Here, even more than with MariaDB, we're talking about performance! MongoDB is a much faster system in certain respects. Particularly when it comes to saving data, since unlike Mysql and its derivatives, you can save several pieces of data at once, rather than processing them one by one.

As you can imagine, this saves a considerable amount of time when it comes to large-scale processing, making it the preferred choice for large-scale projects from the outset.

In the modern Internet, MongoDB has proved its worth, particularly in the world of mobile applications. Their propensity to insert and update data almost every second has necessitated a system that can cope with so much load, so many requests and so much synchronicity.

To choose your system and design your database, you'll need to ask yourself several questions:

• Will my project store a lot of data? Will it store many different types of data?
• Will I need to quickly upgrade my database (adding data, types, volume)?
• Will my database be relatively static or, on the contrary, constantly active and need to respond very quickly?

By following these points and rereading what everyone else is doing, you should be able to get the best possible orientation for your project.

Of course, nothing is set in stone, especially in our business, and one day you may be faced with a more or less radical change of direction.

In other words, if your start-up takes off with several million users in 3 months, you're going to have to rethink your strategy?

As usual, don't hesitate to send us your comments, additions or suggestions on the articles. In the meantime, we'll see you next time!