[wp-hackers] Taxonomy Schema Proposal

[Ryan Boren]

On 4/19/07, Ryan Boren <ryan at boren.nu> wrote:
> On 4/19/07, Jamie Talbot <wphackers at jamietalbot.com> wrote:
> > // terms contains the actual categories/tags/terms/classifiers/whatevers.  It stores ID, name, and
> > slug and alias group.
> > 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 '',
> >  term_group bigint(10) NOT NULL default 0
> >  PRIMARY KEY  (term_ID),
> >  UNIQUE 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_taxonomy_id bigint(20) NOT NULL auto_increment,
> >  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),
> >  KEY (term_idtaxonomy)
> > );
> >
> > // 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_taxonomy_id bigint(20) NOT NULL default 0,
> >  PRIMARY KEY  (object_ID),
> >  KEY (term_taxonomy_id)
> > );
>
> So each term_taxonomy entry has its own unique id stored in
> term_taxonomy_id?  Given an object_id, how does one find all
> relationships in a specific taxonomy since there is no taxonomy field
> in term_relationships?

I guess we use it to join on term_taxonomy and select where taxonomy =
tag, etc.  We'll probably end up getting all taxonomies for the term
in one gulp and divvy up the results in php.

> >         // Adds a new term, also adds alias relationship if necessary.  Handles numeric or slug-based aliases.
> >         function add_term($term, $alias_of = '') {
> >                 global $wpdb;
> >                 $term_slug = sanitize($term);
> >                 if ($alias_of) {
> >                         // ctype_digit is faster than is_numeric, with the caveat that the arg must be in a string.
> >                         $clause = (ctype_digit("$alias_of")) ? "term_id = $alias_of" : "term_slug = '$alias_of'";
>
> Using a year as a category/tag is pretty common. "2007" would be
> misidentified as term ID.

Mark suggested we could regard the int 2007 as an ID and the string
'2007' as a slug.

> >         // Adds a new taxonomy and returns the index it was added at.
> >         static function add_taxonomy($taxonomy) {
> >                 $index = last(array_flip(WPTaxonomy::taxonomies)) * 2;
> >                 WPTaxonomy::taxonomies[$index] = $taxonomy;
> >                 return $index;
> >         }
> > }
> >
> > Plugins can say <?php $my_taxonomy_index = add_taxonomy('my_awesome_taxonomy'); ?> then use that
> > index, or 'my_awesome_taxonomy' to refer to it later on.  Pretty flexible, nice and extensible,
> > mostly normalised and with the opportunity to flesh out a full API.
>
> We have to be careful about the id changing depending on plugin load
> order, but we can work that out.

To be clear, is the taxonomy field still a string, or are you changing
it to an int?

Ryan