jeudi 21 janvier 2021

Choosing between a SQL or NoSQL database structure

At the moment I am thinking about the database structure of my application, and whether I should go for a SQL or NoSQL database.

For my prototype I implemented only a few API-requests from the frontend to the backend (one directional, so no data is send to the frontend yet). The sent data to the backend is an object which contain a few properties of type 'Array', but also a few properties which are (nested) objects itself. Therefore, I choose (in my opinion) the easy route and created a MongoDB database which simply can store data according the given scheme.

Importantly, this data is based on a form that the user fills in. Furthermore, the user only needs access to this data when he/she specifically asks for it, and it does not have to be joined with other data. I am planning to write a script which runs once a day to analyze the data from the forms, and such it does not have to be calculated in real-time.

In the next phase of my application I am going to implement the User Management (to login, register, etc.). Normally, I create a SQL-database and I would like to do this (also with future features in mind). But that would mean that I need to use different kinds of database, and this will likely be a headache to design and maintain.

So I have a few potential solutions in mind:

  1. Keep my form data (unstructured) in a NoSQL-database and User Management in SQL. This is not my preference, and might have a negative effect on performance as the outcomes are dependent on one another. For example, first I have to retrieve the User-id in the SQL database and then I can use that ID to find forms related to that user.
  2. Build my User Management system in NoSQL (not my preference, as it is hard to join data from different collections). Also, it feels more natural to create an User Management system in SQL.
  3. Save the unstructured data in SQL and use data types as JSON, or stringify the data and save it in a column VARCHAR.

I slightly have a preference for option 3. Recently I found out that SQL has a datatype JSON but I am not sure whether this might be useful.

Is it common to have different database types in one application?

Also, are there some recommendations regarding books/articles which describe the problem that I have? Or just good books on Database Design in general?

Aucun commentaire:

Enregistrer un commentaire