Structure of posts table in blog db

For the discussion of Perl, Python, Ruby, and PHP and other interpreted languages.

Structure of posts table in blog db

Post by goluhaque on Wed Mar 16, 2011 11:42 am
([msg=55124]see Structure of posts table in blog db[/msg])

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?
(23:45:03) hauk: I guess you are over the best part of your life when 4-year-olds say "Are you an evil man?"
(23:46:19) hauk: and "Ima punch you in the pecker"
User avatar
goluhaque
Poster
Poster
 
Posts: 153
Joined: Mon Apr 13, 2009 12:08 am
Location: India
Blog: View Blog (0)


Re: Structure of posts table in blog db

Post by fashizzlepop on Wed Mar 16, 2011 12:57 pm
([msg=55128]see Re: Structure of posts table in blog db[/msg])

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?
The glass is neither half-full nor half-empty; it's merely twice as big as it needs to be.
User avatar
fashizzlepop
Developer
Developer
 
Posts: 2303
Joined: Sat May 24, 2008 1:20 pm
Blog: View Blog (0)


Re: Structure of posts table in blog db

Post by Gatito on Wed Mar 16, 2011 2:15 pm
([msg=55135]see Re: Structure of posts table in blog db[/msg])

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.
User avatar
Gatito
Poster
Poster
 
Posts: 113
Joined: Tue Mar 08, 2011 12:55 pm
Blog: View Blog (0)


Re: Structure of posts table in blog db

Post by goluhaque on Thu Mar 17, 2011 11:48 am
([msg=55185]see Re: Structure of posts table in blog db[/msg])

fashizzlepop wrote: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. :)
(23:45:03) hauk: I guess you are over the best part of your life when 4-year-olds say "Are you an evil man?"
(23:46:19) hauk: and "Ima punch you in the pecker"
User avatar
goluhaque
Poster
Poster
 
Posts: 153
Joined: Mon Apr 13, 2009 12:08 am
Location: India
Blog: View Blog (0)


Re: Structure of posts table in blog db

Post by Gatito on Thu Mar 17, 2011 1:20 pm
([msg=55187]see Re: Structure of posts table in blog db[/msg])

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.
User avatar
Gatito
Poster
Poster
 
Posts: 113
Joined: Tue Mar 08, 2011 12:55 pm
Blog: View Blog (0)


Re: Structure of posts table in blog db

Post by goluhaque on Mon Mar 21, 2011 10:06 am
([msg=55314]see Re: Structure of posts table in blog db[/msg])

Gatito wrote: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.
(23:45:03) hauk: I guess you are over the best part of your life when 4-year-olds say "Are you an evil man?"
(23:46:19) hauk: and "Ima punch you in the pecker"
User avatar
goluhaque
Poster
Poster
 
Posts: 153
Joined: Mon Apr 13, 2009 12:08 am
Location: India
Blog: View Blog (0)



Return to Interpreted Languages

Who is online

Users browsing this forum: No registered users and 0 guests