Tag Systems
So what, tell me, is it difficult to put N tags on all content units in your system, and then make a selection of these tags with the system of relevance and importance of links? Yes, there is nothing difficult in this, because this is a typical task for designing a database from the 10th class! What? Are you still boiling? Well then, I will quickly arrange all the checkboxes :)
So, the task : To be able to label any content in the system with “keywords”, for which later to make a sample to obtain a list of data labeled with exactly the same key words.
Solution : In fact, the tag system itself is three additional tables in the DBMS, to which the rest of the content is already tied. Something like this (the blue entities of the system are marked with blue, there can be any number of them):
')
Then everything is simple, in the content_type_map table all system content is supplied with an additional unique ID (for working with tags), because I present a table like this: CREATE TABLE content_type_map (content_id INT UNSIGNED NOT NULL AUTO INCREMENT, foreign_content_id INT UNSIGNED NOT NULL, content_type ENUM ('picture,' article ',' file '), PRIMARY KEY (content_id), KEY (foreign_content_id));
Note: It is worth noting immediately that I will use MySQL 4 in the examples, knowing full well that this system can be made more elegant by using a real DBMS. But it will be a song from another opera.
The content_tag_map table already links content items to keywords. In the simplest version there will be only two fields: content_id and tag_id. However, for special perverts, I would add a field with the date the record was created.
The tags table is very simple: CREATE TABLE tags (tag_id INT UNSIGNED NOT NULL AUTO_INCREMENT, tag VARCHAR (50), UNIQUE (tag), PRIMARY KEY (tag_id));
That's all math. Further, it is possible with this system to make dances with a tambourine.
Just right? Of course, do not forget that the cache will save the world, and write all the tags in the form of serialized arrays in the cache fields in the content itself (by the way, this can be done using triggers in MySQL 5 or any self-respecting DBMS).
So, the task : To be able to label any content in the system with “keywords”, for which later to make a sample to obtain a list of data labeled with exactly the same key words.
Solution : In fact, the tag system itself is three additional tables in the DBMS, to which the rest of the content is already tied. Something like this (the blue entities of the system are marked with blue, there can be any number of them):
')
Then everything is simple, in the content_type_map table all system content is supplied with an additional unique ID (for working with tags), because I present a table like this: CREATE TABLE content_type_map (content_id INT UNSIGNED NOT NULL AUTO INCREMENT, foreign_content_id INT UNSIGNED NOT NULL, content_type ENUM ('picture,' article ',' file '), PRIMARY KEY (content_id), KEY (foreign_content_id));
Note: It is worth noting immediately that I will use MySQL 4 in the examples, knowing full well that this system can be made more elegant by using a real DBMS. But it will be a song from another opera.
The content_tag_map table already links content items to keywords. In the simplest version there will be only two fields: content_id and tag_id. However, for special perverts, I would add a field with the date the record was created.
The tags table is very simple: CREATE TABLE tags (tag_id INT UNSIGNED NOT NULL AUTO_INCREMENT, tag VARCHAR (50), UNIQUE (tag), PRIMARY KEY (tag_id));
That's all math. Further, it is possible with this system to make dances with a tambourine.
- A simple example: just get all the links from any content unit to all other content units with the same key-words.
- A more complicated example: get all the links with the total "weight" of the links
- An even more complicated example: get all the links with the total "weight" and "relevance" of the links
- An example for real perverts: draw a map of content links.
Just right? Of course, do not forget that the cache will save the world, and write all the tags in the form of serialized arrays in the cache fields in the content itself (by the way, this can be done using triggers in MySQL 5 or any self-respecting DBMS).
Source: https://habr.com/ru/post/19009/
All Articles