Overlapping categories

[leicklda]

New to databases...any conceptual help greatly appreciated!

I have a large database of books. I have the standard 1-to-many relationships set up between publishers, authors, titles, etc... This works great. But I also want to set this up so that people can search out books by theme and by category. Problem: each book can have more than one theme or category.

Ex: "Who Stole Santa's sleigh?" Thematically, this is a holiday book AND a rhyming book. Categorically, this is a children's book AND a mystery book.

I've been racking my brain on how to set this up and nothing clicks as the perfect solution.

My ideas:
1: Enter the author, date, etc... as step one in the "Books" table (primary key is Book Title). Then enter another table, "Theme", and enter the book title under each applicable field name (each field being a type of theme). Cons: tedius data entry

2: Have individual theme and category fields in my main book table and set the value to true/false for each title record. Cons: Cannot have a master table of themes and categories - they are just fields in a table

3: Rethink themes and categories completely.

Thanks!

 

[finscott]

This is one of the reasons why RDBMS do not dea with complex data issues. You could have a field in the book table with multiple data values. Field called theme with data entered like "SQL NORMALISATION 1NF RELATIONAL" but purists will hate this.
To normalise the data have a table called BOOKTHEME with two fields for the PK of the BOOK table and the PK of the theme table. For example BOOKTITLE, THEME with a table such as
Winnie the Pooh, Honey
Winnie the Pooh, Bears
SQL Server 7.0 Handbook, SQL
you might want to consider a surrogate key like BookID in the Book table with this solution.