| Author |
Structure of Category table in Blog db |
goluhaque
Member
Posts: 194
Location: India
Joined: 17.02.10
Rank: Hacker Level 1
Warn Level: 30
|
|
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?
That applause I receive from y'all on posting this post would have gotten me drunk on power if I hadn't already been high on life. |
|
| Author |
RE: Structure of Category table in Blog db |
starofale
Member
Posts: 213
Location: England
Joined: 05.12.07
Rank: God |
|
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?
Try a new search engine |
|
|
| Author |
RE: Structure of Category table in Blog db |
stealth-
Member
Posts: 999
Location: Eh?
Joined: 10.04.09
Rank: God |
|
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.
The irony of man's condition is that the deepest need is to be free of the anxiety of death and annihilation; but it is life itself which awakens it, and so we must shrink from being fully alive.
http://www.stealth-x.com |
|
| Author |
RE: Structure of Category table in Blog db |
goluhaque
Member
Posts: 194
Location: India
Joined: 17.02.10
Rank: Hacker Level 1
Warn Level: 30
|
|
|
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.
That applause I receive from y'all on posting this post would have gotten me drunk on power if I hadn't already been high on life. |
|
|
| Author |
RE: Structure of Category table in Blog db |
ynori7
Future Emperor of Earth
Posts: 1481
Location: #valhalla
Joined: 08.10.07
Rank: Diabolical |
|
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|%".
|
|
| Author |
RE: Structure of Category table in Blog db |
j4m32
Member
Posts: 81
Location:
Joined: 01.05.10
Rank: God |
|
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,
|
|
|
| Author |
RE: Structure of Category table in Blog db |
goluhaque
Member
Posts: 194
Location: India
Joined: 17.02.10
Rank: Hacker Level 1
Warn Level: 30
|
|
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
Cat_Id Post
01 04
01 05
02 04[
That applause I receive from y'all on posting this post would have gotten me drunk on power if I hadn't already been high on life. |
|
|
Main:
