I have 3 tables. A table called

  1. blog_posts which has your traditional blog fields like content, image, .etc.

and a table called

  1. blog_categories which stores a number of categories a blog can be assigned to


  1. blog_posts_to_cats which has a structure that allows for multiple categories to be assigned to a single blog post.

blog_posts_to_cats contains the columns post_id, category_id and id (auto-incremented). post_id has a fk relationship with blog_posts.id and category_id has a fk relationship with blog_categories.id

Is this the best methodology of doing this? I understand that if a blog post could only be assigned to a single category then I could simply remove blog_posts_to_cats and put a category field in blog_posts that has a fk relationship with blog_categories and perform a join to get the category name.

As it stands now I have perform 2 queries whenever I want to get 1 or more blog posts (for say a landing page). I have to query blog_posts to get the title and excerpt, and then I have to run a join query between the category tables to get the category name(s) for the post(s)*.

**need to be able to list category names for post individually e.g. array ('0' => 'Uncategorized', '1' => 'test category')*

I really don't see any other way but wanted to get the opinion of others if this is a good schema.

  • 1
    The many:many mapping table blog_posts_to_cats should be structured as described here – Rick James May 24 '19 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.