Debraj Debbarma Debraj Debbarma - 1 month ago 7
MySQL Question

Search a mysql table colunm stored as an array and then join another table based on result

I am working on a blog website and stuck at this:

MySQL


  1. '
    cat_list
    ' table contains columns:
    cat_id
    ,
    cat_name
    (14 entries),

  2. '
    post
    ' table have several columns where
    category
    is one of them where I need to store the category id in which the blog post was tagged to. the category ids are stored in an
    array(19820,83729)
    . [because a blog post can be tagged in multiple categories]



I was trying to search '
post
' by one category ID and join '
cat_list
' to retrieve the
cat name
.
Please help me to do this or is there any other alternative database design I should consider?

Answer

If your post can be tagged with several categories your table model is wrong. You need an associative table between the blog post and the categories. Remove category id from post table and create a table post-category with columns post-id and category-id. Then also add indexes on theses two columns to speed up search in both directions (by category or by post).