Geeklog and Categories

I have been chomping at the bit to rework Geeklog a bit to allow the use of hierarchical (nested) categories for stories, links, etc. Implementing it won't be very hard as it is nothing more than a basic cataloging system (categories, sub-categories and items). However, my implementation plans are more generic. Find out my plans to implement this by clicking 'read more' Implementing hierarchical categories isn't anything new. Yahoo, Google and Excite and crew have been doing it for years. What I want to do is show you the basic implementation for a cataloging system and how I want to extend it just a bit so that this system is really flexible.

Typical Implementations

First, let me cover the basic data structures for such a system. Below are three tables with the most basic attributes needed:

Table: category
Fields:
cg_id mediumint(8) NOT NULL auto_increment,
cg_name varchar(64) NOT NULL,
cg_descr varchar(255),
cg_parent_id mediumint(8),
Primary Key: cg_id
Description: This is the category table. This is a self-referencing table that supports nested categories through the use of the cg_parent_id field which will point to the parent category.

Table: category_item
Fields:
item_id mediumint(8) NOT NULL auto_increment;
item_name varchar(64) NOT NULL
item_cg_id mediumint(8) NOT NULL
--other specific item data--
Primary Key: item_id
Description: This isn't any different from a couple existing Geeklog tables such as stories and links. They are tied to a category through item_cg_id

The Twist

First let me state that what I'm about to propose isn't an original idea. However, I will say that I don't think any of the current weblogs have implemented their categories this way which would make this a bit unique to Geeklog.

The main problem with the above table structures is that we'd have to implement a seperate category table for each 'thing' that needs them. For example we'd need a story_categories and a link_categories. They'd essentially be the same tables structure-wise and only differ in the data they hold. We can prevent having to do this by adding an additional catalog table: Table: catalog
Fields:
ctg_id mediumint(8) NOT NULL auto_increment
ctg_name varchar(64) NOT NULL
ctg_descr varcahr(255)
ctg_gl_core tinyint(1) DEFAULT 0 NOT NULL
Primary Key: ctg_id

Such a table would allow you to create a catalog for each 'thing' needing heirarchical categories. For example, in Geeklog we'd have one catalog for stories and one for links. By doing this, however, we need modify our original category and add a cg_ctg_id that ties a category to a catalog.

What makes this really flexible is we can then create an administrative interface that allows you to quickly administer catalogs and their categories. Furthermore, this implementation lends itself for use in plugins (I can see a Geeklog shopping cart application using this).

What is that ctg_gl_core field for? Well, Geeklog will have a few default catalogs (for example stories). Obviously if you delete the stories catalog Geeklog will cease to function as expected. So for any catalogs that ship as a part of a Geeklog release we will set the ctg_gl_core field to 1 to prevent users from editing or deleting that category. If you were to create a new catalog via the administrative interface that fielf would automatically be set to 0 allowing you full administrative control. This is very similar to how we implemented groups with he grp_gl_core field.

So, does this sound reasonable? Can this be improved? Is it too complicated?