Structure of posts table in blog db

[goluhaque]

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?

[fashizzlepop]

I'd assign each category a number and each post/thread a number based on it's category. Ie. 1 = computer programming, 2 = hardware, 3 = sexy pics, etc...

Then process the categories by their number in your PHP.

Make sense?

[Gatito]

You could create another table named "Category" which pairs numbers with the different categories. That would look something like this:

Code: Select all

------------------------------------------------------------
     CategoryID         |        CategoryName   
-----------------------------------------------------------
         1              |        Programming
         2              |          Hardware
         3              |         Sexy Pics
         4              |          Other...

And add a "Category" field which accepts numbers into your "Post" table. There is good in that too. Imagine you wanted to change the name of a category? You would have to edit the name in every single post ( Could be done with a script too ). Now you just have to change it in one place. Also numbers are sorted/searched faster than strings.

[goluhaque]

I'd assign each category a number and each post/thread a number based on it's category. Ie. 1 = computer programming, 2 = hardware, 3 = sexy pics, etc...

Then process the categories by their number in your PHP.

Make sense?

You could create another table named "Category" which pairs numbers with the different categories. That would look something like this:

CODE: SELECT ALL
------------------------------------------------------------
CategoryID | CategoryName
-----------------------------------------------------------
1 | Programming
2 | Hardware
3 | Sexy Pics
4 | Other...


And add a "Category" field which accepts numbers into your "Post" table. There is good in that too. Imagine you wanted to change the name of a category? You would have to edit the name in every single post ( Could be done with a script too ). Now you just have to change it in one place. Also numbers are sorted/searched faster than strings.

Agreed, I already understand that, but how do I assign multiple categories to a single post? Like a Post that goes
" Hello, this is shit introducing itself to pendejo." , can have multiple categories like shit, introduction and pendejo.

[Gatito]

Good question!
Let's keep the "Category" table but forget about adding a "Category" field in the "Post" table. Instead we create a new table let's call it X. You could use that table to pair Posts with Category or with other words the ID field of your Post table with the ID field of the Category table.

For example:
You have two posts "The PHP language" which is in the "Web" & "Programming" and the " HTML 5 " which is just in the "Web" category. The newly created table would look like this:

Note: "The PHP language" has an ID=012 , " HTML 5 " has an ID=013 , "Web" cas a category ID=004 & "Programming" ID=005

Code: Select all

----------------------------------------------------------
    PostId                  |         CategoryID
----------------------------------------------------------
      012                   |            004
      012                   |            005
      013                   |            004


Now it doesn't matter if you select a post by query it will include all categories the post is in or if you select a category it will include any post which is in that category.

[goluhaque]

Good question!
Let's keep the "Category" table but forget about adding a "Category" field in the "Post" table. Instead we create a new table let's call it X. You could use that table to pair Posts with Category or with other words the ID field of your Post table with the ID field of the Category table.

For example:
You have two posts "The PHP language" which is in the "Web" & "Programming" and the " HTML 5 " which is just in the "Web" category. The newly created table would look like this:

Note: "The PHP language" has an ID=012 , " HTML 5 " has an ID=013 , "Web" cas a category ID=004 & "Programming" ID=005

Code: Select all

----------------------------------------------------------
    PostId                  |         CategoryID
----------------------------------------------------------
      012                   |            004
      012                   |            005
      013                   |            004


Now it doesn't matter if you select a post by query it will include all categories the post is in or if you select a category it will include any post which is in that category.

Thanks. Got it.