数据库建模：针对多个元素的多个类别的最佳方法

Question

Let's say I have 10 books, each book has assigned some categories (ex. :php, programming, cooking, cookies etc).

After storing this data in a DB I want to search the books that match some categories, and also output the matched categories for each pair of books.

What would be the best approach for a fast and easy to code search:

1) Make a column with all categories for each book, the book rows would be unique (categs separated by comma in each row ) -> denormalisation from 1NF

2) Make a column with only 1 category in each row and multiple rows per book

I think it is easier for other queries if I store the categories 1 by 1 (method 2), but harder for that specific type of search. Is this correct?

I am using PHP and MySQL.

PPS : I know multi relational design, I prefer not joining every time the tables. I'm using different connection for some tables but that's not the problem. I'm asking what's the best approach for a db design for this type of search: a user type cooking, cookies, potatoes and I want to output pairs of books that have 1,2 more or all matched categs. I'm looking for a fast query, or php matching technique for this thing... Tell me your pint of view. Hope I'm understood

Answer 1

Use method 2 -- multiple rows per book, storing one category per row. It's the only way to make searching for a given category easy.

This design avoids repeating groups within a column, so it's good for First Normal Form.

But it's not just an academic exercise, it's a practical design that is good for all sorts of things. See my answer to Is storing a comma separated list in a database column really that bad?