[wp-hackers] Taxonomy Schema Proposal

[Jamie Talbot]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ryan Boren wrote:
> 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.

This is a vast improvement on what we have, and a very nice way of providing flexibility for the
future.  Nice job Ryan!  I agree that we probably don't need another table for taxonomy types.  I'm
wondering whether we could get away with only writing taxonomy in one place anyway though:

($wpdb->terms remains the same)

CREATE TABLE $wpdb->term_taxonomy (
term_taxonomy_id bigint(20) NOT NULL default '0',
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'
PRIMARY KEY (term_taxonomy_id)
);

CREATE TABLE $wpdb->term_relationships (
object_id bigint(20) NOT NULL default '0',
term_taxonomy_id bigint(20) NOT NULL default '0'
PRIMARY KEY  (object_ID)
);

Basically the same, except that the term_relationships and term_taxonomy tables are related by an
integer, instead of a tuple.  We're going to have to join the two tables to get taxonomy data
anyway, and nothing beats joining on an int.  We also only have to store the taxonomy field strings
once.  Net result of the same amount of fields, but a bit cleaner.  Thoughts?

Cheers,

Jamie.

- --
http://jamietalbot.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGI0i/rovxfShShFARAtsPAJ4oxynYk6ufMOmvcOI1bknBWSsMtgCfcAfH
9KeGX3dA0Z/b6SXBxw7ksq8=
=Kg5l
-----END PGP SIGNATURE-----