Database generalization level
A few years ago I tried to make a website on a system like MODx and I liked it, despite my experience with other CMS, or maybe because of that. I liked the logic of building work with it, the principles of data structure and much more, but first of all, the fact that a freelancer needs especially often is the simplicity and speed of launching a project with high flexibility. But, although MODx is still to my liking, the post is not entirely about her, and even more likely about something else .
In fact, I consider this cms only as an example of what is present in many of both the open source and commercial systems. I just, as a freelancer, until recently had to work almost always with developing websites for cheaper and faster - maybe other freelancers have a lot of “fat” clients, I don’t know, I didn’t have it. So this situation has led to not a bad experience on modix. But the opening of his own company and the further pulling up of clients with individual requests required to recall and improve their skills in real programming and building databases. And here I felt some discomfort, perhaps, well, at least embarrassment at recalling the structure of both MODx and some other open systems. Further I explain in more detail what exactly seemed strange, but interesting.
')
The fact is that in MODx Revo the main concept for building the structure of the site are resources. That's right, there is no such as, for example, in the same WordPress separate pages of the site, separate blog entries. All pages and posts, and even much more is realized through the site resource model. In fact, this is convenient, especially considering the fact that this is cmf / cms, that is, the system is designed to develop completely different sites both thematically and technically. Thus, it is convenient to follow the entire structure of the site in one resource control panel. In general, the resource system allows you to create and manage the following site entities (I wanted to write objects, but in OOP this word is already taken, so let it be “entities”):
• Regular html-pages;
• Different types of categories and sections of a blog or directory;
• Products and their categories;
• XML documents, for example, sitemap.xml for search robots;
• Text documents, for example, robots.txt correctly make a resource, and not just upload a file;
• Json-pages that I personally use for the same Ajax;
• Create your own text file format.
Here is the combination of many different "entities" of the site in the object of a single model of the Resources and caused my interest. The philosopher's mind began to stir and began to give out many assumptions and questions.
First, I paid attention to the users, because they are made completely separate from the resources. It would seem logical at first glance, but on the other hand, if you started such a dance with the union of a heap of everything, why not make it all to the end. Yes, maximalism in action. Moreover, making users one of the types of resources does not pose a big problem. In the case of modx, this is mostly realized with the help of placeholders, which allow expanding the number of resource attributes and “contexts”, which make it possible to allocate part of the resources according to their purpose. Implementing something similar on a framework (I mean php frameworks) or a bare bundle of scripts and a relational database is not at all difficult.
Secondly, templates and the whole system of presentation and settings of the site are implemented by completely separate models. Why not also combine them into one model, at least purely for the sake of experiment. I feel the tomatoes flying into me.
In essence, this is a continuation of the work of natural philosophers, only transferred to the data storage plane: “What is one,” and is it worth going by the way of generalization when designing a database? By generalization, or the transition from the particular to the general, I mean just the merging of several models at the level of the MVC project and, accordingly, tables at the database level, which I noticed in modx. Although in a situation with a database, not tables, but key attributes (the so-called primary key) are merged, which leads to the union of tables. But this does not mean that such a structure cannot be reduced to normal form.
Let's look at a purely hypothetical example. When designing the database, we needed to store and provide access to several “entities” of the web application: site administrators, with different roles and rights; customers; site pages; tags; products; categories. There are several ways to build a database. And I'm not talking about the normalization of the database, which needs to be done, resulting in a good third type. I mean the number or, one might say, the variety of primary keys .
The most natural and common decision will be to do everything “as told”, that is, for each “entity” its primary key and a set of non-key attributes, which during normalization can be separated into separate tables (see Figure above). But with this solution, several issues and problems are raised. And if the questions like "Why would not administrators and clients be combined into users with different levels of access?" You can just score. That will have to deal with the problems. In terms of administrators, you need to decide how to define their roles and access rights. As an option, this is the creation of another “entity” or even two separate ones for rights and roles. In addition, an important attribute of the role definition appears, which will be solved by these “entities”, or, if they are not created at the database level and are defined in php (who writes websites and applications in another language, imagine that I mentioned it here, and not php), then this attribute must be created by a separate field.
There is another solution, which is just to increase the level of generalization of the database. You can combine all users into one key attribute, thus, it turns out that all administrators and all customers are users. But we need to distinguish them somehow, for this we will have to introduce a non-key attribute of roles, which, in general, we had in the first method, but did not apply to customers. In the same way, we can combine site pages, categories, products and tags into the “resources” object. And here we also need an additional non-key attribute, which allows us to determine which “entity” is one or another resource. And again, all this can lead to a normal appearance.
From such reasoning a desire is born to understand how high the level of generalization of data can rise. It can be assumed that users are just one of the types of resources. If you recall the experience of modx, which led me to such reflections, then in addition to the “entities” described above, you need to add user roles, access rights, templates, and some other, so to speak, related data, such as system settings. And even they can all be combined into a general level of generalization - "objects". It fits perfectly and "resources". Now we just need for each element of the object level to have an attribute that determines to which “entity” it belongs.
This is what we can see as we move up the levels of generalization. First, for realization, as now it turns out, a key attribute that determines the position of an “entity” at lower levels — a “generalization attribute” —is also needed. It should reflect the value at the lowest level that could be. For example, one of the site administrators may have the value “content manager,” others will have a “programmer,” and so on. Secondly, going up we finally conclude that all the data in the database is divided into objects and attributes. In essence, objects are the same fields of tables, but key and reflecting the value of the generalization attribute.
Third, all levels of generalization, except perhaps the highest, allow us to bring the base to a normal form, although it may seem like the most insane decision. Moreover, they may be so. Let's imagine that we have a thousand pages, tags and clients, administrators and settings, let everyone in the total also be a thousand. At us it turns out that at the object level all of them will be stored in one table, even if we did normalization and transferred attributes to other tables. And if the objects are ten times more?
We decided, of course, in part, with increasing levels. And although it has its advantages, such as a high level of abstraction, even transcendental, but there are more minuses. In my opinion there are too many of them, especially if you are doing a highload. I propose to look at how low we can omit the level of generalization and what advantages and disadvantages this can bear in comparison with the “ordinary” level. In fact, to determine how low the level of generalization is possible will be obtained only with a specific task and the result will be definitely only for her.
Let's see what we can do with our example. Clients, of course, we will not assign to users. Next, we divide the administrators into the roles we need, thereby getting rid of them, and leaving only the rights as attributes for each of them. I think it will be something like this: content managers, seo admins (most likely he will be alone), programmers. We also divide the resources into separate parts, which will eliminate the need to indicate what kind of “essence” each of them is. As we can see, the generalization attribute is no longer needed; it turns out that the data itself fulfills its role. And this allows you to get rid of not only the attribute, but also the table, and perhaps more than one, given that the number of attributes of resources and users may not be small.
Breaking down everyone and everything into parts we get more tables. Now users occupy more than one table, except for every table with additional attributes, but four in our example. The same happens with resources that were one table. And on the one hand, the number of tables is increasing, but on the other hand, it is worth noting that we do not take into account how the base will change after its normalization. And also note two more positive effects, especially pleasant when developing loaded applications. The number of rows in each of the tables becomes smaller, but it is here that there is a direct relationship between the level of generalization and the workload of the tables. But with the normalization of such a relationship is not, moreover, may be the opposite. And one more plus, of course, if it is used correctly, the specific purpose of using each table. There is no ambiguity as with high levels of database compilation. And although a larger number of tables, at first glance, complicate the project, with proper naming of tables, maintenance of models (if used) with explanations in the comments - this can serve as more transparency in working with the database.
On the other hand, the level of abstraction of the database implementation falls and this can serve as a disservice. So for example, if a new type of administrator appears, then at a high level it is enough for us to add one additional value to the generalization attribute. Then you can create new users by assigning this value to them. With a minimal abstraction of generalization, such beauty will not work, you will have to create a new main table for this type of administrators, as well as various additional ones, or make a connection with existing ones. And the question arises: “What if a new user type is added instead of a new level down?”, For example, programmers suddenly split into backend and front-end.
Here are only my theoretical arguments on the topic “What if ...”, almost no practical applications are given. The strongest and most useful on this topic, from what I have found, is the article by John and Diana Smith “Database Abstractions: Aggregation and General ization”. They professionally described what I was trying to convey here. I want to try load testing of different levels of generalization. Peace and love to all.
Introduction
In fact, I consider this cms only as an example of what is present in many of both the open source and commercial systems. I just, as a freelancer, until recently had to work almost always with developing websites for cheaper and faster - maybe other freelancers have a lot of “fat” clients, I don’t know, I didn’t have it. So this situation has led to not a bad experience on modix. But the opening of his own company and the further pulling up of clients with individual requests required to recall and improve their skills in real programming and building databases. And here I felt some discomfort, perhaps, well, at least embarrassment at recalling the structure of both MODx and some other open systems. Further I explain in more detail what exactly seemed strange, but interesting.
')
The fact is that in MODx Revo the main concept for building the structure of the site are resources. That's right, there is no such as, for example, in the same WordPress separate pages of the site, separate blog entries. All pages and posts, and even much more is realized through the site resource model. In fact, this is convenient, especially considering the fact that this is cmf / cms, that is, the system is designed to develop completely different sites both thematically and technically. Thus, it is convenient to follow the entire structure of the site in one resource control panel. In general, the resource system allows you to create and manage the following site entities (I wanted to write objects, but in OOP this word is already taken, so let it be “entities”):
• Regular html-pages;
• Different types of categories and sections of a blog or directory;
• Products and their categories;
• XML documents, for example, sitemap.xml for search robots;
• Text documents, for example, robots.txt correctly make a resource, and not just upload a file;
• Json-pages that I personally use for the same Ajax;
• Create your own text file format.
Formulation of the problem
Here is the combination of many different "entities" of the site in the object of a single model of the Resources and caused my interest. The philosopher's mind began to stir and began to give out many assumptions and questions.
First, I paid attention to the users, because they are made completely separate from the resources. It would seem logical at first glance, but on the other hand, if you started such a dance with the union of a heap of everything, why not make it all to the end. Yes, maximalism in action. Moreover, making users one of the types of resources does not pose a big problem. In the case of modx, this is mostly realized with the help of placeholders, which allow expanding the number of resource attributes and “contexts”, which make it possible to allocate part of the resources according to their purpose. Implementing something similar on a framework (I mean php frameworks) or a bare bundle of scripts and a relational database is not at all difficult.
Secondly, templates and the whole system of presentation and settings of the site are implemented by completely separate models. Why not also combine them into one model, at least purely for the sake of experiment. I feel the tomatoes flying into me.
From private to general
Generalization is an abstraction that allows one to collectively imagine a certain class of individual objects as a single named object.
John M. Smith, Diana K. Smith
In essence, this is a continuation of the work of natural philosophers, only transferred to the data storage plane: “What is one,” and is it worth going by the way of generalization when designing a database? By generalization, or the transition from the particular to the general, I mean just the merging of several models at the level of the MVC project and, accordingly, tables at the database level, which I noticed in modx. Although in a situation with a database, not tables, but key attributes (the so-called primary key) are merged, which leads to the union of tables. But this does not mean that such a structure cannot be reduced to normal form.
Let's look at a purely hypothetical example. When designing the database, we needed to store and provide access to several “entities” of the web application: site administrators, with different roles and rights; customers; site pages; tags; products; categories. There are several ways to build a database. And I'm not talking about the normalization of the database, which needs to be done, resulting in a good third type. I mean the number or, one might say, the variety of primary keys .
The most natural and common decision will be to do everything “as told”, that is, for each “entity” its primary key and a set of non-key attributes, which during normalization can be separated into separate tables (see Figure above). But with this solution, several issues and problems are raised. And if the questions like "Why would not administrators and clients be combined into users with different levels of access?" You can just score. That will have to deal with the problems. In terms of administrators, you need to decide how to define their roles and access rights. As an option, this is the creation of another “entity” or even two separate ones for rights and roles. In addition, an important attribute of the role definition appears, which will be solved by these “entities”, or, if they are not created at the database level and are defined in php (who writes websites and applications in another language, imagine that I mentioned it here, and not php), then this attribute must be created by a separate field.
There is another solution, which is just to increase the level of generalization of the database. You can combine all users into one key attribute, thus, it turns out that all administrators and all customers are users. But we need to distinguish them somehow, for this we will have to introduce a non-key attribute of roles, which, in general, we had in the first method, but did not apply to customers. In the same way, we can combine site pages, categories, products and tags into the “resources” object. And here we also need an additional non-key attribute, which allows us to determine which “entity” is one or another resource. And again, all this can lead to a normal appearance.
From such reasoning a desire is born to understand how high the level of generalization of data can rise. It can be assumed that users are just one of the types of resources. If you recall the experience of modx, which led me to such reflections, then in addition to the “entities” described above, you need to add user roles, access rights, templates, and some other, so to speak, related data, such as system settings. And even they can all be combined into a general level of generalization - "objects". It fits perfectly and "resources". Now we just need for each element of the object level to have an attribute that determines to which “entity” it belongs.
This is what we can see as we move up the levels of generalization. First, for realization, as now it turns out, a key attribute that determines the position of an “entity” at lower levels — a “generalization attribute” —is also needed. It should reflect the value at the lowest level that could be. For example, one of the site administrators may have the value “content manager,” others will have a “programmer,” and so on. Secondly, going up we finally conclude that all the data in the database is divided into objects and attributes. In essence, objects are the same fields of tables, but key and reflecting the value of the generalization attribute.
Third, all levels of generalization, except perhaps the highest, allow us to bring the base to a normal form, although it may seem like the most insane decision. Moreover, they may be so. Let's imagine that we have a thousand pages, tags and clients, administrators and settings, let everyone in the total also be a thousand. At us it turns out that at the object level all of them will be stored in one table, even if we did normalization and transferred attributes to other tables. And if the objects are ten times more?
Minimal generalization
We decided, of course, in part, with increasing levels. And although it has its advantages, such as a high level of abstraction, even transcendental, but there are more minuses. In my opinion there are too many of them, especially if you are doing a highload. I propose to look at how low we can omit the level of generalization and what advantages and disadvantages this can bear in comparison with the “ordinary” level. In fact, to determine how low the level of generalization is possible will be obtained only with a specific task and the result will be definitely only for her.
Let's see what we can do with our example. Clients, of course, we will not assign to users. Next, we divide the administrators into the roles we need, thereby getting rid of them, and leaving only the rights as attributes for each of them. I think it will be something like this: content managers, seo admins (most likely he will be alone), programmers. We also divide the resources into separate parts, which will eliminate the need to indicate what kind of “essence” each of them is. As we can see, the generalization attribute is no longer needed; it turns out that the data itself fulfills its role. And this allows you to get rid of not only the attribute, but also the table, and perhaps more than one, given that the number of attributes of resources and users may not be small.
Breaking down everyone and everything into parts we get more tables. Now users occupy more than one table, except for every table with additional attributes, but four in our example. The same happens with resources that were one table. And on the one hand, the number of tables is increasing, but on the other hand, it is worth noting that we do not take into account how the base will change after its normalization. And also note two more positive effects, especially pleasant when developing loaded applications. The number of rows in each of the tables becomes smaller, but it is here that there is a direct relationship between the level of generalization and the workload of the tables. But with the normalization of such a relationship is not, moreover, may be the opposite. And one more plus, of course, if it is used correctly, the specific purpose of using each table. There is no ambiguity as with high levels of database compilation. And although a larger number of tables, at first glance, complicate the project, with proper naming of tables, maintenance of models (if used) with explanations in the comments - this can serve as more transparency in working with the database.
On the other hand, the level of abstraction of the database implementation falls and this can serve as a disservice. So for example, if a new type of administrator appears, then at a high level it is enough for us to add one additional value to the generalization attribute. Then you can create new users by assigning this value to them. With a minimal abstraction of generalization, such beauty will not work, you will have to create a new main table for this type of administrators, as well as various additional ones, or make a connection with existing ones. And the question arises: “What if a new user type is added instead of a new level down?”, For example, programmers suddenly split into backend and front-end.
Conclusion
Here are only my theoretical arguments on the topic “What if ...”, almost no practical applications are given. The strongest and most useful on this topic, from what I have found, is the article by John and Diana Smith “Database Abstractions: Aggregation and General ization”. They professionally described what I was trying to convey here. I want to try load testing of different levels of generalization. Peace and love to all.
Source: https://habr.com/ru/post/270577/
All Articles