Contributed by: Tony Tuesday, January 15 2002 @ 07:04 am EST
First, let me cover the basic data structures for such a system. Below are three tables with the most basic attributes needed:
cg_id mediumint(8) NOT NULL auto_increment,
cg_name varchar(64) NOT NULL,
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.
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
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:
ctg_id mediumint(8) NOT NULL auto_increment
ctg_name varchar(64) NOT NULL
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?