mysql - How to structure "categories" data in the database?

Question

I have a website for which I am building in "categories" which would work pretty much like the tags of StackOverflow.

What I am confused about it how to best structure the tables for this sort of a thing. For example, I know I'd need a table to structure the actual categories like the name, who made it, what date it was made, etc.

What I am not sure about is: when a record gets n number of different categories, how to store that in the database. Should I have the record_ids in the item table to which the categories belong, and just comma-separate the its? Or should I have a separate table with something like item_categories with item_id, category_id, etc...and just join that table and the item table, and the categories table when getting the category?

The ladder seems slow because of the join, but more organized and clean.

Or is there another way to structure this that I have not thought of? How is a good way to go about structuring this sort of data?

Answer 1

Make three tables. One for the page, one for the categories (along with meta information etc), and one to bind them together. That last table only need to have a pageid and a categoryid, to link records from both tables together.

Don't ever store comma separated values in a database, if you need to use those in joins or searches.