Best way to maintain many-to-many relationships in MySQL?

Question

Say I have a simple database with the 'posts' and 'tags' tables. Messages can have many tags and tags that can belong to many messages.

What is the best way to structure a database? I was thinking about using list / serialization:

tags
idx tag_id, str tag_name

posts
idx post_id, str title, list tag_ids

OR have another table with associations. The problem is that I don’t even know how to structure the request in order to pull the associated tag names when I receive the message.

posts
idx post_id, str title

post_tags
fk post_id, fk tag_id

Actually, I don't like any of them. Is there a better way?

Answer 1

post_tags - .

, , , , , .

Answer 2

- .

:

SELECT tag_name FROM tags, post_tags WHERE post_tags.tag_id = tags.tag_id AND post_tags.post_id = 12345;

Answer 3

"" . () . :

select ...
from post_tags
inner join posts on post_tags.postID = posts.postID
inner join tags on post_tags.tagID = tags.tagID
...

, , .

Answer 4

Answer 5

, : http://www.tonymarston.net/php-mysql/many-to-many.html

- SQL- .

: http://www.qcodo.com/