Thinking logically about a database structure: Adding 'tags' to things users post - A seperate table or...?

Question

Hi everyone!

I'm still beginning to get my head around this whole relational-database-thingymawhatsit. Anyway, I'm a PHP programmer so I can do all that shabang. I just need to think what would be the best method for this scenario...

I'm developing a site (with CodeIgniter if that's any help) - a site where users can post content (think a blog-style thing). Each post has to have tags, any number of tags, I can't just be constrained to, say, 3 or 5. This has got to be unlimited.

So which of these do I do?

1. Put the tags alongside the posts in the same database table, i.e. 'entries' contains 'post', 'title', and 'tags' (and obviously 'id')
2. Put the tags in a table of their own and link each tag row to an entry('s) 'id'...?
3. ...Something else entirely?

Just need to know the best, most logically structured way of doing this. Thinking about tables communicating with each other is confusing enough...!

Oh, and bonus points to anyone with any CodeIgniter snippets that may get me along on my way ;)

Thanks!

Jack

Answer 1

You want a many-to-many relationship. This is modelled in a database by three tables:

• Post
• Tag
• PostTag

PostTag contains a foreign key to the Post table and to the Tag table. It has one row for each tag on each post.

Answer 2

Basically, you want an n-m relation between tags and posts :

• each post can have several tags
• each tag can correspond to several posts.

Using a relationnal database, this is done with three tables :

• a "post" table, that contains one line per post
• a "tag" table, which contains one line per tag
• and a "post_tag" table, which contains one line per correspondance between a tag and a post -- which means several lines per tag ; and several lines per post

You'd have, in your DB, those three tables :

• post
  • id
  • title
  • content
  • ...
• post_tag
  • #id_post => foreign key to the post
  • #id_tag => foreign key to the tag
• tag
  • id
  • name