What is the database design and multi-layer Association logic of commodity tags?

A common product corresponds to multiple tags. Only three tables (commodity table, tag table, and associated table) are required for the design. This situation is complicated, please kindly advise me to give a brief introduction to the background. The project is a third-party exhibition platform, that is, it does not exhibit itself, but is responsible for the UNICOM exhibition... A common product corresponds to multiple tags. Only three tables (commodity table, tag table, and associated table) are required for the design. This situation is complicated, please advise me

In brief, the project is a third-party exhibition platform, that is, a platform that does not exhibit itself, but is responsible for Unicom exhibits, exhibitors, brand parties, pavilions, and exhibitions, similar to intermediary. Exhibitors or brand owners can submit their own exhibits on the exhibition hall. People who have spare venues can also rent out their own venues as exhibition halls. Then they will join in to hold the exhibition hall. Maybe it's a bit confusing to say this. Don't worry about it. It's hard for me to elaborate on a protocol.

You can customize tags when uploading exhibits as required. Exhibitors and brand owners are not associated with the tag function, but are associated by means of "exhibitors (brand owners)-exhibits-tags, that is to say, the label of the exhibitor depends on the label of all the exhibits he uploads. The same applies to the brand.
The exhibition itself does not contain the tag function, and is associated by means of "exhibition-exhibitors-exhibits-tags. The exhibition has two fields: Start Time and end time. If the end time is exceeded, the exhibition ends.

The problem arises. The requirement is to filter by tags in the exhibition list, and the labels that are not displayed.

I was suddenly forced. According to this demand, I had to first find all the exhibitors or brand owners of the exhibition (that is, the exhibition has started and has not ended), and then find all their exhibits, find all the labels of the exhibits, remove the duplicate ones, and display them. This will be terrible in the future when the data volume is large.

I don't know if this requirement is unreasonable, or whether there is a good solution, but I can't think of it. Please kindly advise me

In addition, due to some restrictions of the other party, you cannot use such items as redi, memcache, and mongodb. You can only use mysql

Reply content:

A common product corresponds to multiple tags. Only three tables (commodity table, tag table, and associated table) are required for the design. This situation is complicated, please advise me

In brief, the project is a third-party exhibition platform, that is, a platform that does not exhibit itself, but is responsible for Unicom exhibits, exhibitors, brand parties, pavilions, and exhibitions, similar to intermediary. Exhibitors or brand owners can submit their own exhibits on the exhibition hall. People who have spare venues can also rent out their own venues as exhibition halls. Then they will join in to hold the exhibition hall. Maybe it's a bit confusing to say this. Don't worry about it. It's hard for me to elaborate on a protocol.

You can customize tags when uploading exhibits as required. Exhibitors and brand owners are not associated with the tag function, but are associated by means of "exhibitors (brand owners)-exhibits-tags, that is to say, the label of the exhibitor depends on the label of all the exhibits he uploads. The same applies to the brand.
The exhibition itself does not contain the tag function, and is associated by means of "exhibition-exhibitors-exhibits-tags. The exhibition has two fields: Start Time and end time. If the end time is exceeded, the exhibition ends.

The problem arises. The requirement is to filter by tags in the exhibition list, and the labels that are not displayed.

I was suddenly forced. According to this demand, I had to first find all the exhibitors or brand owners of the exhibition (that is, the exhibition has started and has not ended), and then find all their exhibits, find all the labels of the exhibits, remove the duplicate ones, and display them. This will be terrible in the future when the data volume is large.

I don't know if this requirement is unreasonable, or whether there is a good solution, but I can't think of it. Please kindly advise me

In addition, due to some restrictions of the other party, you cannot use such items as redi, memcache, and mongodb. You can only use mysql

This requirement can be achieved with relational databases.
According to your description, see the following data model:

Query Exhibition by Tag:

SELECT e.GalleryId, e.StartTime, e.EndTimeFROM Exhibition eWHERE EXISTS (    SELECT 1    FROM PresentedProduct p    JOIN ProductTag pt ON pt.ProductId = p.ProductId    WHERE TagId = 'T1' AND p.GalleryId = e.GalleryId AND p.StartTime = e.StartTime    )

I don't know much about the specific requirements, so you need to design a data model based on the specific situation.
In the logic design stage, do not consider physical implementation, data volume, and other issues. Instead, be loyal to your needs and design a Logical Data Model.
During physical implementation, you can create indexes or even table shards as needed.
Do not optimize it too early.

My personal opinion is as follows:

1. Use Surrogate Key with caution)
   If each table uses a human Id, it is difficult to analyze a data model that complies with the logic, and data integrity is not guaranteed. In addition, a query usually requires a lot of JOIN Operations.

2. Data Volume
   Relational databases have strong processing capabilities. For millions of tables, as long as the index is set up, a relatively small amount of data is returned quickly. If you need to return a large amount of data, it will take a lot of time to design it. Do not think of your project as google or Amazon. In that case, most teams have no time or ability to develop it. Even if the project is really successful, it will be too late for reconstruction. Learning the classic relational database is sufficient for most projects.