Interesting problem

JMJimmy

I'm currently developing a custom shopping cart/site management system for a client and I ran into an interesting dilema - I thought I'd share and see what ideas others had on this topic.

The dilema is in creating a category system over multiple sites where there's no limit to how many sub-categories there are (ie: a tree with ever expanding branches). So, I came up with this solution:

CREATE TABLE categories (
categoryId smallint(6) NOT NULL auto_increment,
category tinytext NOT NULL,
visible tinyint(1) NOT NULL default '0',
PRIMARY KEY (categoryId)
) TYPE=MyISAM;

CREATE TABLE sites (
siteId tinyint(4) NOT NULL auto_increment,
url tinytext NOT NULL,
status tinytext NOT NULL,
PRIMARY KEY (siteId)
) TYPE=MyISAM;

CREATE TABLE categories_to_sites (
relSiteId tinyint(4) NOT NULL default '0',
relCategoryId smallint(6) NOT NULL default '0',
KEY relSiteId (relSiteId,relCategoryId)
) TYPE=MyISAM;

CREATE TABLE category_to_category (
relCategoryId smallint(6) NOT NULL default '0',
relSubCategoryId smallint(6) NOT NULL default '0',
KEY relCategoryId (relCategoryId,relSubCategoryId)
) TYPE=MyISAM;

*note relSubCategoryId actually relates to a category id but can't have 2 relCategoryId in the same table

So, this is all well and great going in, you add site(s), you add categories, you relate which site(s) the categories are visible on, and you relate which category is in which category. The interesting part crops up bringing it back out of the database.

Say you need to display something like this:

Category1 >> Category 17 >> Category 58 >> Category 129

In my mind, the only way to display that would be to do a SELECT for each category.

Can anyone else see another way? I'd be interested to know how others have designed category structures.

JMJimmy

altexis

I agree that this type of categorization problems is quite interesting. It actually goes as far AI and philosophy you know 🙂

Now... I have a few questions for you...
The way you organize your categories you can have extra-multiple-categorizations

For example Category129 could be found under the path
Category1 >> Category 17 >> Category 58 >> Category 129
or
Category2 >> Category 65 >> Category 129
or
Category1 >> Category 41 >> Category 88 >> Category 129
or
...infinent categorizations

so which one is the proper one to go back?

one solution could be to track the way the user got in Category 129 at the first place! so going back will not be too hard because you will have the links already cached

another solution would be to show all the parent nodes for Category 129 ... which in my example are:
Category 58, Category 65, Category 88
and let the user decide which way up he/she wants to go

JMJimmy

That's actually an interesting idea, (showing the different ways they could go "up" the tree), I hadn't considered that. I waas thinking, as a way to avoid multiple SQL queries and maintain the path they took, is to use a dot notation system stored in the session like 58.65.88 - that way a single SQL query to retrieve the entire category list by simply exploding it.

It should be an interesting excercise to say the least 🙂

JMJimmy

ultraslacker

Just for kicks you may want to check out the ltree postgresql module which supports just this hierarchy of data - http://www.sai.msu.su/~megera/postgres/gist/ltree/