[wp-hackers] Taxonomy Schema Proposal

[Jamie Talbot]

Ryan Boren wrote:
> > On 4/19/07, Ryan Boren <ryan at boren.nu> wrote:
>> >> 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.

Yeah, either way could work.  It does involve a join, but as it's an int join, it's probably ok.
And, you don't have to work hard to make sure you're updating the data in two places each time.  If
you wanted to find the total count of post_category for an object's post_categorys, for example,
you'd have to join to term_taxonomy anyway and under the initial schema that would have to be a
string join.  I think it's probably common enough that we will have to join to the term_taxonomy
table that we should do it in as fast a manner as possible (correctly keyed, and with matching
column sizes).

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

Yeah, that's true.  We don't *have* to allow lookups by ID, but it would be nice if we could figure
out a way.  I'm sure there will be some cases where you have either the slug or the ID and want to
do the same thing.  Just trying to build in flexibility.

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

The above proposal should work, except if plugins add the WPTaxonomy::taxonomies directly.  Best
solution would be to make it private (does PHP4 enforce scoping?  I can't remember), or use some
kind of max() function on the array to get the highest value, instead of last().  I looked and I
couldn't find a builtin PHP array_max() function, which I find hard to believe...

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

Well, usually, I'd go with a separate lookup table and int joins every time.  However, it seems like
some people already aren't happy with 3 tables and I thought I'd be pushing my luck going for 4.
You can do a simple caching function which slurps them all up in one go though, and because it's not
likely to change much, you could place them somewhere where they'll be autoloaded.  Plugins could
then add entries to that table, instead of recreating their taxonomy each time they are loaded.  I'd
personally like to see a 4th table yeah.  I imagine there will be lots of lookups for taxonomy and
string joins aren't great.

Cheers,

Jamie.

--
http://jamietalbot.com