Structure of Category table in Blog db

So, I was building a blog, and wanted to assign categories to blog posts. What should be the structure of the post table? Should there be fields for each category(or its id) with boolean datatype? Or should all the category ids be limited to a single field?

I wouldn't limit a post to a single category. Maybe have a look at some open source blogging platforms and see what they do?

Just going with one field would make far more sense, to me at least. Having multiple boolean fields could make the db larger, more cluttered to look at, and more complicated when it came to wanting to add more categories. One field offers much better scalability/simplicity. Either way, for a small blog it won't matter too much.

Disclaimer: I'm hardly a MySQL expert.

starofale wrote: I wouldn't limit a post to a single category. Maybe have a look at some open source blogging platforms and see what they do? In Joomla, a single post can only be assigned a single category, and I couldn't seem to make out the heads or tails in Wordpress.

You could just make your category field fairly large and allow multiple categories in the same field separated by a delimiter. So it could look like "|category1|category2|category3|" and you could just search WHERE category LIKE "%|categoryName|%".

This is very similar to what ynori suggested but I would use an extra table…

Make a table for all the categories, and make them all able to have a parent category id if you want sub-sectioned categories:

tbl_categories cid, parentid, name(, metadata - depends if you want to embed meta ddata for search engines)

Make cid a key & auto_increment.

Easier to maintain a table that you can modify the categories and tags in metadata since blogs are fueled by: search engine hits & peer suggestion(link sharing etc).

INSERT INTO `tbl_categories`(parentid, name, metadata) VALUES(1,'Hax0ring','hacking, hacks, moar catch phrases here')

Then for multiple categories, similar to what ynori said, use a a delimiter but you then need only reference the categories primary ids (cid) to

tbl_posts id, title, catids, content, blah, blah.

INSERT INTO `tbl_posts`(title catids, content, blah, blah) VALUES ('Welcome to the blog', '1,5,6,9','My really interesting super article',stuff, stuff....)

Either as ynori said, use pipe characters as a delimiter, csv (comma separated values) as in my example. Once you've exploded the string/separated each item from the list, it's not too hard to do an SQL join statement or a quick second query for the relevant information.

Alternatively:

If you don't fany that, and you know that the categories will remain rather static, there is nothing "wrong" in using a simple array. Create a file in a convenient directory containing the array, that you can include where necessary, I guess.

Most would argue this is less flexible, it's a bit harder to process the text to change a particular field - compared to a database.

Jim,

Got a better solution.Say, I make a new table wherein there is a cat_id and the name of the category it represents. There won't be a new field in the Post table, but I will create a new table X where

01                      04
01                      05
02                      04[```