[wp-hackers] Taxonomy Schema Proposal

[Ryan Boren]

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?

>         // 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.


>         // 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.

> For a more classical OO approach, rather than just using a class as a container, something like
> class WPTerm {} might be nice, so you could do:
>
> $term = new WPTerm('city');
> $term->get_aliases();
> $counts = $term->get_count(TAXONOMY_LINK_CATEGORY | TAXONOMY_POST_TAG);
> // counts->link_category = 5;
> // counts->post_tag = 8;
> $term->find_ancestors();
>
> Again where the WPTerm constructor would accept either an term_id or a term_slug.  Not sure what
> people would think of that?

Fine by me.

Ryan