Re: Design dilemma I can't get past

In message <1a7d5f0d.0212282037.274bc5f5_at_posting.google.com>, WebGuyBill <bill_at_bhmediagroup.com> writes
>Hello -- I'm a self-taught newbie. I appreciate any help you can
>offer.
>
>Using MySQL and PHP I'm designing a database for my website, which is
>all about books. My database structure is going to wind up being very
>similar to Amazon (tables for Authors, Titles, graphics, etc).
>
>Where I'm having trouble is with Categories (or Genres). Any one book
>can typically fall into as many as 8-to-10 categories. How best can I
>relate a specific title (e.g. "Moby Dick") to more than one category
>(e.g. "Literature > Thrillers > Seafaring" as well as "Animals >
>Mammals > Whales")?

You need to make a decision on precisely what you will use the codes for. If you intend to use a subject-based filing system like that in a non-fiction library then you need to use a single code to tell users which shelf the book is on. If you want a simple system then you should probably create one for yourself based on the structure of the Dewey Decimal system.

If you intend to use the codes to enable someone to find which titles fit into a particular genre then each title has to be coded for every genre that any user might use to describe it. The way to implement this is a table identifying genres and a link-table to implement the many to many relationship between genres and titles.

If you aren't clear which approach you want to use then I'd suggest talking it through with a librarian. If my experience is typical you will find that librarians are quite happy to advise you, and will have a lot of reference material that you can refer to.

Don't expect simple answers though, this is a subject area that is still evolving after 200 years.

-- 
Bernard Peek
bap_at_shrdlu.com
www.diversebooks.com: SF & Computing book reviews and more.....

In search of cognoscenti