Logic Behind Blog Categories ?

[phplover]

Hi,

I'm developing a blog that will have categories. I want to be able to store blog posts in more than one category. My problem is i am not sure how to go about it.

I have a table called posts:

CREATE TABLE `posts` (
  `post_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_uid` int(10) unsigned NOT NULL COMMENT 'user uid',
  `category_ids` varchar(255) NOT NULL COMMENT 'each category is seperated by a comma',
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'date post was modified',
  `post_status` varchar(10) NOT NULL COMMENT 'publish = publish post | draft = save but dont publish ',
  `post_title` varchar(255) NOT NULL,
  `post_title_permalink` varchar(255) NOT NULL COMMENT 'post title for friendly urls (this title never changes to keep url valid if post_title changes)',
  `post_excerpt` text NOT NULL,
  `post_content` mediumtext NOT NULL,
  `comment_status` varchar(15) NOT NULL COMMENT 'open = comments allowed | closed = comment not allowed',
  PRIMARY KEY (`post_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have another table called categories:

 CREATE TABLE IF NOT EXISTS `categories` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'auto generated id',
 `category_name` varchar(20) NOT NULL,
 `slug` varchar(20) CHARACTER SET utf8 NOT NULL COMMENT 'The “slug” is the URL-friendly version of the name. It is usually all lowercase and contains only letters, numbers, and hyphens.',
 PRIMARY KEY (`id`),
 UNIQUE KEY `category_name` (`category_name`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf16 AUTO_INCREMENT=3 ;

Now i think my logic of the two tables above are fine (hopefully), but my problem is i don't know how to go about allowing a blog post to appear in more than one category.

Trying to think of all possible sceneraios my questions are:

1) What do i need to do to allow a blog to be posted in more than one category ? (i know what i need to do in regards to the html and displaying the categories from database, but the logic i am stuck on is i have a [b]category_id[/b] column on the [b]posts[/b] table which will store the id of the category i am storing the blog post into which is fine if i only post a blog post in one category but to choose more than one category i am not sure how i would store that in the database as the category_id in the posts table is to store one category (which is the category_id from the category table), i don't know if i need another table perhaps, i am clueless how people generally deal with this sort of thing like categories ?

2) another possible problem i may encounter in future, what if i delete a category, how should i go about dealing with blogs posts posted in a category that i want to delete ? ( i may not want to delete the blog post just the category, therefore any posts in that category is going to need to be moved somewhere else, how to people generally deal with the scenario when developing such a system ?

Thanks for any help. I don't need to know the php code just the logic behind it all. I do have a users table etc but kept it simple as i could as the rest i have no problem with.

Thanks for any help
PhpLover

[pritaeas]

Instead of the field category_ids, create a link table consisting of the post id and category id.

[phplover]

Hi pritaeas,

Thanks, took my head a while to understand how the logic would work but know what you mean now.

Thank you ver much!

phplover