Is there a guideline for when one is more appropriate/efficient when designing your schema? In my example, I have a forum with threads organized by category. I have a threads table which contains a category ID. I need to be able to select all threads, threads from a set of category IDs, and threads from a single category ID.
Does it matter if I have a threads table with an index on the column cat_id, or if I use a joining table called thread_categories with two columns thread_id, cat_id?
You'd need a thread_categories table if a thread can belong to more than one category.
There's absolutely no need to create such a table to speed up a selection of threads by one or more categories.