Blog Database Structure - Categories & Meta Tags

[eluser]n0xie[/eluser]
This is my 'simple' take on it.

I assume you have a table called blogs where your blog posts are. For example you have 4 blog entries:

Code:

blog_id, title, content
=======================
1, my programming blog, a blogpost about programming
2, my ruby article, an article about ruby
3, my second ruby article, another article about ruby
4, my php article, an article about php

Let's say your categories have a table categories:

Code:

cat_id, parent_id, name
=======================
1, 0, programming
2, 1, ruby
3, 1, php

Which will give you the categories:
/programming
/programming/ruby
/programming/php

Now all you need is a field in your blog table:

Code:

blog_id, cat_id, title, content
===============================
1, 1, my programming blog, a blogpost about programming
2, 2, my ruby article, an article about ruby
3, 2, my second ruby article, another article about ruby
4, 3, my php article, an article about php

Which put your first blog under the category programming, your second and 3rd blog under the category /programming/ruby and your fourth blog under the category /programming/php

Now for tags you need a different structure. We start with a table called tags:

Code:

tag_id, name
============
1, php
2, ruby
3, programming

Now we need a way to bind the tags to your post. We use different relations table for that named blog_tags:

Code:

blog_tag_id, blog_id, tag_id
============================
1, 1, 3 --> bind the first blog to the 'programming' tag
2, 2, 3 --> bind the second blog to the 'programming' tag
3, 2, 2 --> bind the second blog to the 'ruby' tag
4, 3, 3 --> bind the third blog to the 'programming' tag
5, 3, 2 --> bind the third blog to the 'ruby' tag
6, 4, 3 --> bind the fourth blog to the 'programming' tag
7, 4, 1 --> bind the fourth blog to the 'php' tag

Hope this gets you on your way.