-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I haven't given this too much thought with your situation, but could be something to look into.
Have you looked into using the nested set's model for hierarchies? It allows you to easily pull out a parent with all it's children in 1 or 2 queries - no need for recursion. It requires more application code to be able to delete/add/move nodes in the hierarchy but it may server your needs better if your hierarchy is reasonably deeply nested. You can then use application code to simply iterate of the retrieved results from parent to child and cache values you may want to use in children that have NULL values. Here's some links: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html http://www.openwin.org/mike/books/index.php/trees-and-hierarchies-in-sql Nath Scott Webster Wood wrote: > I'm trying to figure out the best way to build an heirarchial > categorystructure kinda like you'd find on yahoo or ebay. Basically I > amcreating a number of items and I am going to tie each item via a linktable > to another link table that defines the categories and subcategories. > > Sample: > categories: > ID = int {autofill/unique/primary key} > Name = char(32) > Desc = char(128) > Property1 = char(64) > Property2 = char(64) > > categoryLinks > ID = int {autofill/unique/primary key} > catID = int {required} (pointing to an ID from categories) > parentID = int (pointing to another ID from categories) > Desc = char(128) > Property1 = char(64) > Property2 = char(64) > > items > ID = int {autofill/unique/primary key} > Name = char(64) {required} > Desc = char(128) > Property1 = char(64) > Property2 = char(64) > > itemLinks > itemID = int (pointing to an ID from items) > categoryLinkID = int (pointing to an ID from categoryLinks) > > > categoryLinkswill have one entry with parentID = null for the 'top' of > anyhierarchical tree. The Desc and other property values may also be > null(although I'm going to generally fill in all of them on the > top/parententry). I still wrestling with how to deal with identical > categoryLinkentries in complex redundant sub-trees but am more interested in > anycreative ideas for another delimma. > > I would like to be able totreat these kind of like object instances and > utilize inheritancewhenever a property of a sub-item is empty (null). i.e. > if I had alinking of categories like Computers with links to Desktop and > Laptop,and under Desktop had links to Windows, Apple and Linux: > > categories: > +----+-----------+ > | ID | Desc | Property1 | > +----+-----------+-----------+ > | 01 | Computers | technical | > | 02 | Desktop | [NULL] | > | 03 | Laptop | portable | > | 04 | Windows | [NULL] | > | 05 | Apple | [NULL] | > | 06 | Linux | superior! | > +----+-----------+-----------+ > > categoryLinks: > +----+-------+----------+-----------+ > | ID | catID | parentID | Property1 | > +----+-------+----------+-----------+ > | 01 | 01 | [NULL] | hightech | (parent - overriding Property1 from > categories value) > | 02 | 02 | 01 | [NULL] | (links Desktop under Computer) > | 03 | 03 | 01 | [NULL] | (links Laptop under Computer) > | 04 | 04 | 02 | [NULL] | (links Windows under Desktop) > | 05 | 05 | 02 | [NULL] | (links Apple under Desktop) > | 06 | 06 | 02 | [NULL] | (links Linux under Desktop) > +----+-------+----------+-----------+ > > let'sjust say I left Property1 blank on the link forWindows and also in it's > parent Desktop but had it filled in on theDesktop parent Computers (as shown > above). If I wanted to find the Property1 for the sub category but found it > blank, I'd like to grab thevalue from the next parent(s) above it that did > have a value. > > Is there any way to writethat kind of recursive search right in the SQL > itself rather than doingtons of lookups in an 'until' type structure in my > scripting languageuntil I find a value that's not null? > > Ultimately I'd want it tosearch first in the properties for the current item, > then the categoryLink then the 'categories' entry for catID from that link, > thengo to the parent and continue until either it found a value for agiven > property or until parentID was null. I will settle for somethingthat just > looks up the categoryLinks tree itself. I know I can do it with aperl or php > loop but was just curious if SQL could do anything sofancy. (I'm probably > going to be using MySQL but do have access topostgresql also) > > The other idea I am toying with is just using afile/directory tree but cringe > at the number of file reads I will haveto do when building HTML option lists > to get all the descriptions! Imay settle on some combination of the two as > this ultimately going tobe for a web tool and I will also be having other > associated files witheach category or item. (gif icons, etc.) Either way > I'm still goingto have to look backwards to handle the desired inheritance. > Any ideasare welcome. > > SW > > P.S. I'm now playing with another concept of just using keywords for each > entry under both categories and items. Basically using a table for the > top-level categories, another for individual items and one for keywords. > There are then two keyword-link tables, one for category keywords and one for > item keywords. > I'm also playing with another set of two tables for generic keyword filters > that could be assigned to each category. (a filter name pointing to a > particular categoryID and a link table of keywords tied to the filterID that > I could then use when creating a search for items related to a desired subset > of a given category) > > One of the problems I saw with the complex heirarchy is when thestructure > gets too deep, it becomes difficult to select a specificitem when you have to > keep clicking through category after category toget to the one you want. > (just try it on ebay or yahoo! lol) > I added an additional 'type' column on the keywords to categorize howthe > keyword relates to the items and even created a special conditionwhere 'type' > = 'type' and the keyword= another type value. This then allowsme to include > an option add an entire group of keywords of a given'type' with a single link > list entry. > This idea will probably give me a lot more flexibility and would avoid > problems with redundant sub-trees as well. The user would only have to select > a category then a given subset filter to get a manageable list of related > items. > But I would still be curious about the recursive search mentioned above. > > > > > ____________________________________________________________________________________ > Be a better friend, newshound, and > know-it-all with Yahoo! Mobile. Try it now. > http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFIBb3H9gTv6QYzVL4RAj82AKCjSdX+KHIWePWVgVRYaaC3YUUdJgCg1LDr Xsk+bwOSUeo40fdDQUXAg88= =x58M -----END PGP SIGNATURE-----
