Best way to store a many-to-many relationship in MySQL?

Question

Let's say I have a simple database with tables 'posts' and 'tags'. Posts can have many tags and tags can belong to many posts.

What is the best way to structure the database? I thought of using a list/serialize:

tags
idx tag_id, str tag_name

posts
idx post_id, str title, list tag_ids

OR having another table with the associations. Problem is using this I don't even know how to structure the query to pull the associated tag names when I get a post.

posts
idx post_id, str title

post_tags
fk post_id, fk tag_id

I actually I don't like either of them. Is there a better way?

Answer 1

The post_tags is the proper means of implementing a many to many relationship in the database.

The only addition I'd make to what you posted is that both columns in it should be the primary key to ensure there are no duplicates.

Answer 2

Use the table with associations - this is called a junction table.

To get the tags for a post:

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

Answer 3

Use a join table

Answer 4

You almost certainly will want to use an "intersection" or join table. This table can contain the primary keys of the posts and tags table, and (optionally) its own distinct primary key. Joining the three tables should be straightforward:

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

You can create a view that does the basic join which you can then reference in your code.

Answer 5

I think this is a good explaination to start with: http://www.tonymarston.net/php-mysql/many-to-many.html

Otherwise you should have a look on object relational mapping - which does the complicated sql queries for you.

For example: http://www.qcodo.com/