S
S
smallreg2021-11-21 11:10:44
CMS
smallreg, 2021-11-21 11:10:44

How to implement forum topics in MySQL that participate in several sections at once?

Usually each new topic (topic) on the forum belongs to one section of the forum. But sometimes you need to add a few tags so that you can search for this topic on the forum. In order not to introduce an additional entity - tags - I would like to implement the ability to create a topic in several sections at once (the interface for adding sections when creating a topic is not important yet). In fact, a tag system is obtained here, but unlike tags, all sections are known in advance - new ones are not added by the user, and there will always be a section (tags sometimes may not be). The question is: is it possible to use some other table structure so as not to make a list of tags and a table where the correspondence of the topic (topic) to each tag is written? That is, put everything in one table, and not three? For some reason it seems to me that 'Search Fulltext' should be suitable for such a task, or something similar, since in one field of the table you can simply add section designations: numerical or text, for example, separated by commas or spaces. It is necessary to be able to quickly find all topics (topics) for one given section (you do not need to search for a topic that belongs to two or more sections at once).
How can this be done as simply as possible? What table structure to use?

Answer the question

In order to leave comments, you need to log in

3 answer(s)
T
ThunderCat, 2021-11-21
@ThunderCat

For some reason it seems to me that 'search Fulltext' should be suitable for such a task,
It seems to you. In addition to the fact that fulltext is not for this at all, it will also work relatively slowly, since the variability of values ​​will be low. About "convenience" of operation with a line instead of a normal index generally I am silent.
It is necessary to be able to quickly find all topics (topics) for one given section (you do not need to search for a topic that belongs to two or more sections at once).
m2m, it is reliable and fast, it is enough to know the partition index.
How can this be done as simply as possible?
Tip: Don't chase apparent simplicity, you'll get a lot more hassle from the wrong architecture than from another 15 minutes spent creating a lookup table, a pivot table and writing 2 joins in a query. It's more important to do it right, not easier.

A
Antonio Solo, 2021-11-21
@solotony

if the number of partitions is constant and small (that is, there will be no problems with indexes), then you can create a separate BOOL field in the table for each partition,
but this is of course perverted and the only normal solution is M2M (using a table).
and Fulltext is essentially the same creation of an M2M index, only hidden from the depths of mysql

G
grek_cheburek, 2021-11-21
@grek_cheburek

I once did this. I added a field to the table, in which I placed the id in md5. Further, when the user notes which categories he needs and enters this md5 into the topic, separated by a vertical bar. On the page of the topic itself, I took the field in which md5 was stored and, through explode, separated and prepared a query to display the categories. I used this system for blogs and photo albums on the site. But the site was small, that is, not with such an audience as VK. So I can’t say anything about the speed of this approach, it suited me personally.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question