Database design. Compoder Pattern

Web 2.0 triumphantly walks through the virtual world. Social networks grow like mushrooms after rain. Now you can store your photos, videos, write blogs and listen to music in one place. All this can be commented on, put in favorites, copied ... There are many possibilities, the content of social networks is diverse and diverse, and this is their advantage.

Now imagine the structure of the database of some Vkontakte. Submitted? And what do you see? Many tables with data? And what else? Many tables for many-to-many links! Necessary, from the point of view of a relational database, but superfluous from the point of view of logic. But that is not all. Among the fields of the tables we see a huge number of “extra” fields, which are just foreign keys that serve for one-to-many connections, which are also necessary from the point of view of relational theory, but absolutely useless from the point of view of logic.

Imagine now that you decide to create your own social network. You plan that there will be users in it (where do without them?). These users will be able to unite in groups, store photos and videos and write blogs. Imagine the structure of the database, we understand that it seems to be nothing complicated. So it is, if the structure of relations is simple. But the requirements can be complicated. Now the customer needs the content to be owned by several users at the same time, and at the same time the community. That all this (any object) could be commented. In order for a user to be able to keep at all whatever they please, including other users and communities and blogs of communities. You never know what else you can think of! And each such requirement entails a redesign of the base, the addition of new tables, foreign keys. I'm not talking about writing code. Is there any way to get rid of it? Somehow to make it so that connections between any objects (read records) could be created arbitrarily, without redesigning the database each time.

Can! Open the book “Gangs of Four”, find the description of the Composite pattern and read:
')
Purpose

Composites objects into tree structures to represent part-whole hierarchies. Allows customers to consistently treat individual and composite objects. (I advise you to read the entire chapter)

What we need! So let's consider how to apply this idea for our purposes. I will say at once that later I will tell only about the idea itself. It has already been implemented and is working in a real project, a new project is being developed based on this architecture, but I just don’t have time for a detailed description with pieces of code. In one of the following articles I will definitely tell you in more detail and give examples and real code.

So, the task of this architecture is to allow arbitrarily linking the objects of the system with each other.

In the considered architecture, each object is an entry in the table. For each type of objects (photo, video, user, etc.), a separate table is created in the database. Each such table consists only of fields in which only those data are stored that relate to the object + primary key of the record (that is, no foreign keys). Communication is carried out through a single table for all named GUID, consisting of four fields. Why are there four fields? Well, 2 fields are clear, these are ID related objects, that is, their foreign keys. The classical scheme is many-to-many. But after all, only by ID it is impossible to uniquely identify an object within the entire database. IDs can be repeated in different tables (the sequence for each table is different). Therefore, the concept of object type is introduced. In essence, the type of object is the name of the table in which this object is stored (or the name of the class in the program code, as you wish). And thus, the ID-TYPE pair is a unique identifier of an object within the entire database. That is why the GUID relationship table consists of 4 fields, the first 2, as I said above, are the ID of the related objects, and the remaining 2 are their types. Knowing the ID and type of the object (parent), you can select all objects related to it (children), both all and of a particular type.

This allows you to build the most bizarre connections, and to respond very flexibly to changing business requirements for the system.

What flaws do I see in this architecture ? First of all, this is a huge GUID table. After all, min the number of records in it is equal to the number of records in all database tables. And this is only minimal, since the object can be associated with several other objects at once (however, it’s not a problem to divide into several tables in the future).
The second is a bit heavier requests. If the one-to-many relationship is implemented according to the classical scheme, then the sample will be only on the foreign key, and in such a model there is no foreign key, and the GUID table must be jointed

True, a model can easily be converted to a classical one both completely and in its individual sections (for example, in narrow places), if there is a need for it, and it is completely transparent for the client. In reality, it is, some parts of the project work according to the classical scheme, and some of the new, while not interfering with each other.

Well, a few words about the implementation . Nobody, of course, wants to write requests with hands, so the whole logic of working with the database is placed in a separate layer called GuidComponent. This is the base class, which is a kind of pattern ActiveRecord. All objects of the system inherit it (for example, PhotoComponent, VideoComponent, etc.). The base class, GuidComponent, provides each successor with methods for working with other objects of the system, for linking and deleting connections, for obtaining object data and its associated objects (Childs). For example, to bind an object, just call the addChild () method; and pass as a parameter a link to the object to be linked.

Here is a brief class diagram (a bit outdated truth):



And finally, a sample Java code that adds a comment to a photo:
// id = 10
PhotoComponent photo = new PhotoComponent(10);
CommentComponent comment = new CommentComponent();
comment.setMessage(" ");
comment.save();

photo.addChild(comment);

//
List comments = photo.loadChilds(CommentComponent.class);


, . ( ) , , , . - , , , .