[wp-hackers] A "terms" table

[Ryan Boren]

On 4/15/07, Ryan Boren <ryan at boren.nu> wrote:
> On 4/15/07, Ryan Boren <ryan at boren.nu> wrote:
> > > As for some of the bit and count fields currently causing grief, I would
> > > argue the solution for that isn't a separate tags table, but a separate
> > > table specifically for that type of data. In Drupal for this
> > > infrastructure they have a term_data, term_hiercharchy, term_node,
> > > term_relation, term_synonym, vocabulary, and vocabulary_node_types
> > > tables. I think that might be a little more than we need, but there are
> > > some concepts there we could pretty cleanly combine into a single extra
> > > table that isn't called categories or tags, and will provide a good and
> > > scalable foundation for years to come.
> >
> > The presence of category_parent and the count fields are the crux of
> > my problem with the unified table.  They cause a lot of grief and kill
> > any future-proofing you think you're getting by having a unified terms
> > table.  If you're open to a new table for this information, I think I
> > could get behind that.  I'll give it some thought.
>
> For everyone's perusal, the drupal schema can be found here (scroll down):
>
> http://cvs.drupal.org/viewcvs/drupal/drupal/modules/system/system.install?rev=1.92&view=markup
>
> Taxonomy code:
>
> http://cvs.drupal.org/viewcvs/drupal/drupal/modules/taxonomy/taxonomy.module?rev=1.351&view=markup

I'll borrow and corrupt Drupal's vocabulary terminology.  We have
three vocabularies: link categories, post categories, and post tags.
We want a term with a given name to always have the same id but be
able to participate independently in each of these vocabularies.  If
we pare down the tables to the least we can get away with, we end with
with three.

CREATE TABLE $wpdb->terms (
  term_ID bigint(20) NOT NULL auto_increment,
  term_name varchar(55) NOT NULL default '',
  term_nicename varchar(200) NOT NULL default '',
  term_description longtext NOT NULL,  // Maybe put this in term_vocabulary
  PRIMARY KEY  (term_ID),
  KEY term_nicename (term_nicename)
);
CREATE TABLE $wpdb->term_vocabulary (
  term_ID bigint(20) NOT NULL default '0',
  vocabulary_ID bigint(20) NOT NULL default '0',
  parent bigint(20) NOT NULL default '0',
  count bigint(20) NOT NULL default '0'
);
CREATE TABLE $wpdb->object2term (
  object_ID bigint(20) NOT NULL default '0',
  term_ID bigint(20) NOT NULL default '0',
  vocabulary_ID bigint(20) NOT NULL default '0',
  PRIMARY KEY  (object_ID),
  KEY object_ID (object_ID,term_ID),
  KEY term_ID (term_ID,object_ID)
);

A term is created within the context of a vocabulary.  When creating,
if the term doesn't exist in the terms table it is added there and to
term_vocabulary with the appropriate term ID.  If the term already
exists in terms, it is just added to term_vocabulary.   Adding the
term to a post adds an entry to object2term with the appropriate
vocabulary id.  The count and parent relationship are per vocabulary.
Deleting a term removes its term_vocabulary entry.  If the term is no
longer used in any vocabulary, it is removed from terms.

That's the rough idea. Thoughts?

Ryan