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
