I'm designing a blog database. I want posts to belong to any number of categories, including none (i.e. number of categories = 0, 1, 2, 3, ...).
I understand that the common way to design such a database (e.g. in Wordpress), is to have one table for posts, one table for categories, and one table for relationships, thus:
table relationships
column relationship id
column post id
column category id
But this means that to display a post, my script will have to make at least three database queries. This seems slow to me.
Which is why, in another blog, I had only one table for posts which included a varchar column for categories, in which I inserted a string with all the category names, which I parsed in PHP, thus
table posts
column post id
... (many other columns)
column categories
where column categories contained a string that might look like this:
apples,oranges,bananas
which I simply explode()ed in PHP.
Please explain why I should avoid the second method (one table, explode). There must be something wrong with it that I miss, because it is not commonly used in blog software.
Note:
There might still be a table listing categories, into which new categories are written when a post is created, and from which lists of categories are drawn to display them in, of example, the sidebar.
I expect there to be many more queries for posts than for posts-in-categories, which is why I don't worry much about querying the second database for posts from a certain category, which might be faster in the first database.
