Hedgehog with a snake in one basket, and also a little about the lack of a scheme

Recently, I often see articles about why you should use nosql or that you should never use it and rely only on relational repositories. However, in my opinion, these excellent tools can get along together perfectly, allowing you to use their common virtues and avoiding their disadvantages.

As a preface

About a year ago, our team had a great opportunity to work on the site of a new English TV channel. Like most similar projects, it focuses on entertainment and information programs, news, and also interacts with visitors through social networks.

The customer had two critical requirements that we followed when choosing the technologies used for the project:

1. It is very important that the data are consistent;
2. The site should work with a sufficiently large number of simultaneous users.

To ensure the integrity of the data, we decided to save them in a relational form in MS SQL, and to ensure the speed of the user part of the site, we took mongodb. For each type of page was created its own, denormalized collection of documents. Thus, the data is first stored in MS SQL, and then published from there to all the necessary mongo DB collections.

And why use Mongu, if you can do with SQL alone, you ask? To justify the choice consider a few examples.
')

Example one

Let's start with an exemplary entity diagram of a TV show. We have a table of programs, seasons, episodes, episodes and episodes:



The program page contains a program description, a list of program seasons, a list of episodes with a brief description of the episode, the release date of the episode, a link to its page. We have 3 options for obtaining the necessary data from the relational database:

1. One big query with joins. Good - one request per page. Bad - a large amount of duplicate data and as a serious increase in the load on the database server;
2. Four queries from ORM, one for each table. Good - no extra information is retrieved or returned in any request. Bad - actually four queries, for each of which work out the unit of work, which means four times the context of connecting to the database is created and deleted. The variant is quite traditional and even quite good; the minuses are solved by screwing the cache;
3. A stored procedure that performs four queries and returns four data sets. Well - no extra data, one query to the database from the client. Bad - it is quite difficult to find an ORM for .net, in which this possibility is implemented in some kind of digestible form.

In general, everything looks pretty good, but when using a denormalized collection of TV programs in Monga, you don’t need to think of anything - just make one request, pulling out the data by ID, and that's it.

Example two

The site has several pages displaying up-to-date information - news, programs, instagram and twitter posts, weather, advertisements, information about interruptions in the work of the metro, etc.



These data, very fragmented in structure, are of a common nature and are displayed on the same pages - the most recent information is displayed on the main page, only events related to it are displayed on a page of a region, only articles are displayed on a news page. In SQL, each type of tile is stored in a separate table, since their structure can be very different. Such a sample from the relational database turns into a small local hell - the number of requests to the database grows with each type of tile added, and this does not take into account the complexity of writing such a sample directly in the code. In order to somehow make life easier, it would be possible to use the table and metadata, this simplifies filtering the data, but adds the need to paste them into an object on the application side. And for this situation, any nosql repository like Mongi becomes the perfect solution. In the previously appeared articles about Monga on Habré, any mention of the absence of a scheme raised questions "how is that? The scheme is always there. ” Questions of this type are perfectly reasonable, but a little about that. The absence of a scheme in this context is the ability to save data with a different scheme, but of the same nature within the same collection. They can be used to build indexes and filter data, as well as easily deserialize them into an object with the correct and necessary type, which is easy to work with in the application.

What to pay?

Using sql and nosql in conjunction looks very attractive and convenient. However, as we know, it is necessary to pay for convenience with something. In our case, it is the need to write the functionality of publishing data from sql to mongodb. In our opinion, this is an acceptable price.

Instead of conclusion

I tried not to get attached to the language in which the application was written and to the sql and nosql tools. Instead of a monga, you can substitute any other non-relational storage.