Thinking logically about a database structure: Adding 'tags' to things users post - A seperate table or...?
codeigniter, database, database-design, mysql, php
Solution
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
Problem
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? - Put the tags alongside the posts in the same database table, i.e. 'entries' contains 'post', 'title', and 'tags' (and obviously 'id') - Put the tags in a table of their own and link each tag row to an entry('s) 'id'...? - ...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