Let us say I've 10 books, each book has designated some groups (ex. :php, programming, cooking, snacks etc).
After storing this data inside a DB I wish to search the books that match some groups, as well as output the matched up groups for every set of books.
What will be the ultimate way for a quick and simple to code search:
1) Create a column with all of groups for every book, it rows could be unique (categs separated by comma in every row ) -> denormalisation from 1NF
2) Create a column with only one category in every row and multiple rows per book
It is simpler for other queries basically keep groups one at a time (method 2), but harder for your specific kind of search. Is correct?
I'm using PHP and MySQL.
PPS : i understand multi relational design , i favor not joining each time the tables ... im using different connection for many tables but this isn't the issue... Im asking what is a great way for any db design for this kind of search : a person type cooking, snacks, taters and i wish to output pairs of books which have 1,2 more or all matched up categs. Im searching for a quick qurie, or php matching way of this factor... Let me know your pint of view. Hope i am understood
What for you to do is have one table for books, one table for groups, and something table for hooking up books and groups. Something similar to this:
book_id | title | etc
category_id | title | etc
book_id | category_id
This really is known as a many-to-many relationship. You need to most likely google it to find out more.
Use method 2 -- multiple rows per book, storing one category per row. It's the only method to make hunting for a given category easy.
This design eliminates repeating groups within a column, therefore it is great for First Normal Form.
But it's not only an academic exercise, it is a practical design that's great for all kinds of things. See my response to Is storing a comma separated list in a database column really that bad?
I would suggest approach number two. The reason being approach 1 takes a full text search from the category column.
You might have some success by splitting up into two tables: One table has one line per book along with a unique id (call the table
books), and also the other has one line per book per category and references it id in the first table (call the table
bookcategories). Then should you just have book data you utilize table
books, where if you want groups you
join both tables.
This relationship is really a Many-To-Many (a magazine might have multiple groups along with a category may be used in a number of books).
Only then do we possess the following:
First got it?