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_at_newsreader01.highway.telekom.at>
>
>
> 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.
Date: Tue, 31 Dec 2002 08:04:19 +0100
Message-ID: <3e114173$0$28666$91cee783_at_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 - 08:04:19 CET