I just learned mysql and am not familiar with mysql. Now I have three tables like this
Article table
| id | title | content |
|---|---|---|
| 0 | hello | world |
| 1 | hey | .... |
Question form
| id | title | content |
|---|---|---|
| 0 | hello | world |
| 1 | hey | .... |
Tag table
| id | name | avatar |
|---|---|---|
| 1 | java | .... |
| 2 | js | .... |
Both tables share a tag table. The avatar of the tag table is the avatar of the tag. Each tag has its own avatar or a default avatar.
My current question is how to associate the tag table with the other two tables? It is necessary to ensure that articles or topics can be quickly found based on the tag id.
The first solution I thought of was to add a tags field below the article table and question table. Separate with commas (I don’t know if it’s good or not...), and then I found that I don’t know how to search... How do I search like this... Do I use like...
Article table
| id | title | content | tags |
|---|---|---|---|
| 0 | hello | world | 0,1 |
| 1 | hey | .... | 2,3,4 |
Question form
| id | title | content | tags |
|---|---|---|---|
| 0 | hello | world | 1,4 |
| 1 | hey | .... | 6,3 |
Then I thought about the second plan, because the structures of the article table and the question table are basically the same, can I merge them?
A combination of question and article subject....
| id | title | content | tags | type |
|---|---|---|---|---|
| 0 | hello | world | 1,4 | article |
| 1 | hey | .... | 6,3 | question |
Then I think I shouldn’t merge it, right? Because the database is not that big if it is separated. If I just want to find all the questions with a label of 1, a single table query will be faster, right?
Then I thought about a third solution, how about making a related table? Does this related table need an id... I don’t quite understand.. If there is a related table, then are the tables for articles and questions? There is no need for the tags field.
Association Table
| aq_id | tag_id |
|---|---|
| 0 | 1 |
| 1 | 6 |
| 0 | 4 |
| 1 | 3 |
This query seems to be relatively simple? It can be done with just one select statement, and the query speed is not bad... But as time goes by, this related table will definitely have a lot of data, and this query should Will it be very slow?
I hope someone can give me some answers... Thank you all in advance
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
First of all, I was dazzled by the article table and question table you gave me... Forget it if the structure is the same, the data is still the same...
Secondly, the solution tags you used to get along at the beginning are not advisable... It's difficult Modify tags, and as you said, it is difficult to query;
As for the merger of the article table and the question table, it is completely unnecessary. This involves the design of the database, you can understand the paradigm of the database;
Finally, the association table The idea is very good, and using aq_id and tag_id is also correct. As for you saying that there may be a lot of data in the future, don’t worry, not to mention that mySql is not a vegetarian, the problem of too much data will also be solved in your future study
The solution for you to get along at the beginning is that tags can be queried. Each number in tags is separated by symbols. Use like to query, for example, ,1,11,31. When querying 1, you can use tags like ”%, 1, %". This should be able to find out.