Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!newsfeed.news2me.com!mephistopheles.news.clara.net!news.clara.net!feed.news.nacamar.de!newsrouter.chello.at!newsfeed01.univie.ac.at!newsfeed01.highway.telekom.at!newsreader01.highway.telekom.at!not-for-mail Date: Tue, 31 Dec 2002 08:04:19 +0100 From: Heinz Huber User-Agent: Mozilla/5.0 (Windows; U; WinNT4.0; en-US; rv:1.1) Gecko/20020826 X-Accept-Language: en-us, en, de-at, de, fr MIME-Version: 1.0 Newsgroups: comp.databases.theory Subject: Re: Design dilemma I can't get past References: <1a7d5f0d.0212282037.274bc5f5@posting.google.com> <3e0f0d2c.705023@news.webshuttle.ch> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Lines: 55 Message-ID: <3e114173$0$28666$91cee783@newsreader01.highway.telekom.at> NNTP-Posting-Host: 193.110.129.66 X-Trace: 1041318259 newsreader01.highway.telekom.at 28666 193.110.129.66 Xref: newsfeed1.easynews.com comp.databases.theory:24205 X-Received-Date: Tue, 31 Dec 2002 02:29:11 MST (news.easynews.com) Bob Hairgrove wrote: > On 28 Dec 2002 20:37:21 -0800, bill@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 ... > 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