Re: Design dilemma I can't get past

From: Bob Hairgrove <rhairgroveNoSpam_at_Pleasebigfoot.com>
Date: Sun, 29 Dec 2002 15:10:00 GMT
Message-ID: <3e0f0d2c.705023_at_news.webshuttle.ch>


On 28 Dec 2002 20:37:21 -0800, bill_at_bhmediagroup.com (WebGuyBill) wrote:

>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")?
>
>Do I need separate tables for each primary category (Biography,
>History, et al) and then more separate tables for each sub-category
>and sub-sub-category?
>
>Thanks so very much for any help!

Categories, as you describe them, have a hierarchic relationship. There are various ways of modelling this in an RDBMS. Perhaps the most common way (not necessarily the best way, however) is to use what is known as an "adjacency list", i.e. an extra field "parent" which takes the ID (i.e. some unique identifier) for another row which would be the next higher-up in the relationship. If there is no parent, let the field either be empty or just point back to itself.

Also, check out Joe Celko's "nested set" model. I'm not really up on the different variations of these, since the adjacency list has always served me well enough. But I understand that there are potential problems with this design (I just don't know what they are <g> ... maybe Joe will tell you himself if he's reading this).

Anyway, you would typically want to have a linking table between books and categories because it is a many-to-many relationship (like authors and books). If you link the books together with their lowest-order categories, you can always get at all the other categories by walking the category tree back to its root. Queries, of course, will be tedious; it's often necessary to use procedural language to do this efficiently.

As to referential integrity, you have to watch out that you include only categories in the linking table which are in distinct branches of the tree for any one book ... but I'm not sure that MySQL supports R.I. anyway, so it's probably a mute point.

Good luck!

Bob Hairgrove
rhairgroveNoSpam_at_Pleasebigfoot.com Received on Sun Dec 29 2002 - 16:10:00 CET

Original text of this message