Re: Design dilemma I can't get past

From: Heinz Huber <hhuber_at_racon-linz.at>
Date: Tue, 31 Dec 2002 08:04:19 +0100
Message-ID: <3e114173$0$28666$91cee783@newsreader01.highway.telekom.at>

Bob Hairgrove wrote:
> 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.

See, the last point is the reason why Joe's nested set model is better in a lot of situations. You don't need any procedural coding to get the parents or children of any node in the nested set model. You can do it all using a standard SQL query.

Regards,
Heinz Received on Tue Dec 31 2002 - 01:04:19 CST