Best way to design table with many category columns

Question

I have a Book table with columns like

Book-id title description author category1 category2 category3

100001 Micro blah blah Michael Crichton SciFi Action Mystery

But the categories available is about 22 each book is allowed 3 Any category column can have any of the categories

For searching this set up, it becomes extremely hard and there are about 6000 books so far listed on it.

What would be the best way to do categories in a database design to make searching easier? I tried giving them all a index column but that caused the table to throw a 1070 error. Thanks in Advance Gary

This is why MySQL is called a "relational database", you have to create relations. In your case, create a `book_category` table, and link your books to the categories (an enum, or another table). — Guillaume F., Apr 07 '17 at 05:09
As you can see from my question, I am not that familiar with database design and have managed by good luck to have a system that just about works. But it is shockingly bad and I look forward to all suggestions for correcting my lack of basic knowledge. I have been pondering over different ways to do it for days, in the hope I would not have to ask for help. — Gary Owain Jones, Apr 07 '17 at 06:21
You should stop the guess work and learn how to use the technology first. Do tutorials, read the database documentation, buy books... — Guillaume F., Apr 07 '17 at 06:42
Yep I just did but managed to confuse myself into what would be the best approach, the answer you gave and Mayank elaborated on did seem to me the most likely best approach. I just thought I would confirm that by asking such a simple question, if this was the wrong thing for me to do then I apologize. — Gary Owain Jones, Apr 07 '17 at 06:50

score 1 · Accepted Answer · edited Apr 07 '17 at 06:57

1

Try this architecture:

book_detail: To hold book related information with an auto-increment id
category: to hold categories list in it with an auto-increment id
book_category: to hold the mapping of book -> categories, it a one to many mapping, means one book can map to different categories by its primary key like:

book_detail.id -> category.id

edited Apr 07 '17 at 06:57

Guillaume F.

5,905
2
31
59

answered Apr 07 '17 at 05:09

Mayank Pandeyz

25,704
4
40
59

I edited your answer, the good practices suggest to use singular tablenames. See this answer for more informations : http://stackoverflow.com/questions/338156/table-naming-dilemma-singular-vs-plural-names – Guillaume F. Apr 07 '17 at 07:01
@GuillaumeF. thanks mate – Mayank Pandeyz Apr 07 '17 at 08:11

Best way to design table with many category columns

1 Answers1