Since I started working as a software developer, I have heard and read some debates about the order in which a system should be designed. Some think that everything should start from the application definitions so the system will be built upon what it is supposed to do. Others will argue that a good database entity-model design should be the first step. Well, from my experience, there is no ideal first step, and you won´t be able to avoid altering whatever was your initial idea. Therefore, do not judge me because I choose to dive deeper into the Database side of things first. It is just the first iteration, and as you may have probably read in my previous articles, I have already proposed an overview of my system's functionalities that served as a guide on my DB design.
In this article, I don´t want to delve into my design, that would be a super boring experience for any reader. Instead, I´d rather share the DB ERM diagram and explore some key ideas.
There are three things that I would like a reader to pay attention to, and I will explore each of them during the next sections.
Log Tables
Note in my design the following tables: "Roles_Features_LOG" and "Users_Features_LOG", both of them are populated with triggers. Any insert, update, or delete in the "Roles_Features" and "Users_Features" tables will automatically insert a row in the respective LOG table. This will make it possible to track all changes related to permissions to a User and a Role.
There is another LOG table called "Access_LOG" that was created to store access Logs generated by the Authorization Service. The idea is that each time the Authorization Service checks if a user has access to a certain feature, then a row will be inserted into this table. I will probably change this design choice in the future and do such logs in a NoSQL database but this is good enough for a first iteration.
The User_Permission View
Most of the time, I would rather implement logic in the application layer, not in the database. But sometimes, like in this case, making use of a View can cut you a lot of time and effort. As permissions can be granted to a specific user both directly and through an assigned role, we need to combine these two types of permission into a single result to define whether the user has access to such a feature or not. I could retrieve all the information from the database and process it in the Authorization Service, but that would be a waste of network traffic and processing since I can retrieve only the information needed by making use of a super simple view.
Another benefit of using a View, in this case, is that the query execution plan is already stored in the database, so there is extra time saved since there is no need to optimize this query every time it is issued. This can create a great performance boost, considering that this will be the most issued query in our system. The reader can take a minute to examine the View's query:
select `UF`.`idUser` AS `idUser`,`UF`.`permission` AS `permission`,'Personal' AS `from` from `Authorization2`.`Users_Features` `UF`
union select `UR`.`idUser` AS `idUser`,`RF`.`permission` AS `permission`,`R`.`name` AS `from`
from ((`Authorization2`.`Users_Roles` `UR` join `Authorization2`.`Roles_Features` `RF` on((`UR`.`idRole` = `UR`.`idRole`)))
join `Authorization2`.`Roles` `R` on((`UR`.`idRole` = `R`.`idRole`)));
Soft Delete
Finally, the last thing that I would like the reader to pay attention to is the "deleted" and "deletedAt" columns in the following tables: "Users", "Role", "Features", "Entities".
There are some reasons not to delete a row in such tables. The most important one is not to lose data that can later be used for generating reports. Note that the "Access_Log" table has two Foreign Keys pointing to "User" and "Features" respectively, if I try to delete a row in "User" then I would either cascade the operation or set it to Null. Later if I wanted to generate an access report I could not track it back to the original user. Therefore, my design choice is to create a Soft Delete where I can simply set the "deleted" column to 1.
The Database Create query.
I made the SQL query to create the described schema available on my Github repository so anyone can recreate the described schema in its own MySQL instance.
Next article, I will start describing the Authorization Service and its architecture.
I hope you liked this post and keep following this project.