si-blog

Multiple blog categories

Posted May 20, 2004 in Databases.

I've been pondering some of the complexities of a CMS redesign, and I've come upon a stumbling block. I'm something of a database newbie, so I thought I'd ask my learned readers.

In my current system, posts can belong to a single category. Each post record has a category identification number that points to a category record. How do I give each post the chance to belong to 1 or more categories? I realize this will probably require another table.

Comments

  1. Gravatar

    article: id, title, text....
    category: id, label
    rel_article_category: id, id_article, id_category

    There you are.

    Posted by Bruno Bord on May 20, 2004.

  2. Gravatar

    In your example, rel_article_category is a join table?

    I think I'm being brainless here. It seems to me that the structure is similar to that of an entry and its related comments. Perhaps I am functioning below my unimpaired best today :(

    Posted by Simon Jessey on May 20, 2004.

  3. Gravatar

    You should check out the source and DB structure of WordPress.

    Posted by Matt on May 20, 2004.

  4. Gravatar

    Actually, Matt, I did just that. I found it a little difficult to follow - I'm really rather new to PHP and MySQL.

    Posted by Simon Jessey on May 20, 2004.

  5. Gravatar

    " It seems to me that the structure is similar to that of an entry and its related comments"

    You're almost right. There are from 0 to "n" comments related to an article. But a comment belongs to 1 article. And only one.
    For the categories, an article may belong to 0 or more categories (better have one, in fact), but a category may have from 0 to "n" articles related to.
    Thus, we need a relation table to record the "tuples".

    Posted by Bruno Bord on May 21, 2004.

  6. Gravatar

    What I am thinking is going to be hard is having a post belong to cats and subcats of cats, all the while being able to belong to multiple cats and subcats..

    Hmm... time to get out an old fasioned pencil and paper...

    Posted by Mike P. on May 21, 2004.

  7. Gravatar

    Goodness gracious, Mike. When you've worked it all out, post it here! LOL

    Posted by Simon Jessey on May 21, 2004.

  8. Gravatar

    For those of you still following this conversation, I am starting to get my head around this problem now. I envisage:
    * Item table
    * Category table
    * Entry table
    The "entry" table would consist merely of an item ID and a category ID, acting as a join table in a many to many relationship. I'm still a little hazy on the details, but this seems to be the way to go.

    Posted by Simon Jessey on May 25, 2004.