[wp-hackers] Taxonomy Schema Proposal

[Ryan Boren]

Putting aside the rest of the debate, let's talk schema.  Here's a
proposed three table solution where we have terms (tags/categories),
taxonomies (link categories, post categories, post tags), and objects
(posts/pages and links).

// terms contains the actual
categories/tags/terms/classifiers/whatevers.  It stores only ID, name,
and slug.
CREATE TABLE $wpdb->terms (
 term_id bigint(20) NOT NULL auto_increment,
 term_name varchar(55) NOT NULL default '',
 term_slug varchar(200) NOT NULL default '',
 PRIMARY KEY  (term_ID),
 KEY term_slug (term_slug)
);
// term_taxonomy puts a term in the context of a taxonomy (link
category, post category, or tag).  Hierarchy is put here as well as
counts.
CREATE TABLE $wpdb->term_taxonomy (
 term_id bigint(20) NOT NULL default '0',
 taxonomy varchar(20) NOT NULL default '0',
 term_description longtext NOT NULL,
 parent bigint(20) NOT NULL default '0',
 count bigint(20) NOT NULL default '0'
);
// term_relationships relates a term to a post or link or undeclared
future object thingy.  The relationship is placed within the context
of a given taxonomy.
CREATE TABLE $wpdb->term_relationships (
 object_id bigint(20) NOT NULL default '0',
 term_id bigint(20) NOT NULL default '0',
 taxonomy varchar(20) NOT NULL default '0',
 PRIMARY KEY  (object_ID),
);

I haven't thought about the best keying yet.  TBD.

"taxonomy" is a string that can be 'post-category', 'link-category',
or 'post-tag'.  Plugins can add their own taxonomy types.  This could
be an id that relates to a taxonomy table instead of a stand-alone
string, but another table doesn't seem necessary. Another table would
mean having the taxonomy string in one place rather than two, but I
didn't drink that much of the normalization punch back in school.

This approach maintains one id for a given term name while separating
out the troublesome hierarchy and count fields into a table that can
maintain them in relation to specific taxonomies.  This avoids the
troubles encountered with the current category_parent field when link
and post categories share a table.  It also avoids having to add a
count field for every taxonomy type.  This schema seems well-suited to
plugins.  Plugins can easily add their own taxonomies. (Ryan's One
True Folksonomic Reverie Plugin here we come.) Generic API along the
lines of get_term($id, $taxonomy) and
get_terms($taxonomy[|$taxonomy]...) could be simple and powerful.  The
different taxonomies could be kept separate or joined in interesting
ways.

Ryan