[wp-hackers] Taxonomy Schema Proposal

[Jamie Talbot]

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

Steve Lewis wrote:
> > On 4/16/07, Ryan Boren <ryan at boren.nu> wrote:
> > I would like to ask you to expound on three use cases:
> > - Aliases  (for instance: products -> projects)
> > - Lookup by slug (myblog.com/projects)
> > - Both together (myblog.com/products)
> >
> > I don't want to presume too much about your intentions.

In the trac ticket, it was initially thought that synonyms could be a feature added by a plugin.
Regardless of whether that ends up being the case or not, one standard way of representing groups
like this is to do a self join.  This might mean adding a column to the terms table called
'term_group' (which would also allow for synonyms of categories, which could be mighty handy for
multilingual plugins).

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),
 KEY term_slug (term_slug)
);

1 Products	products	1
2 Cities	cities		0
3 Projects	projects	1


// Returns all synonyms of 'products' (including 'products').
SELECT t2.* FROM $wpdb->terms AS t1 INNER JOIN $wpdb->terms AS t2 ON t1.term_group = t2.term_group
WHERE t1.term_group != 0 AND t1.slug = 'products'.

I also don't presume that this way will be chosen, but it is a possible solution.  I have
implemented code in a manner similar to this to store the relationships between post translations -
each post is in a translation group, from which you can find out all the other translations.  Since
I got the initial syntax worked out, it's performed pretty well.

I'm not certain it is a core feature either - do other tagging systems employ aliases?

Lookup by slug is easy.  Lookup by both is still easy, but a bit more inefficient as you'd have to
do an IN(term_ids) based on all the returned tags from the above query.  The alternative would be to
have a master slave tagging system, which sounds suspiciously like category parents and don't really
fit with the whole fast and loose tag thing.

Cheers,

Jamie.

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

iD8DBQFGJAkVrovxfShShFARAsgJAJwOr90zgpDFjfEok76wRc5Lj+6jKgCeI+mU
P5JJeQt5RyzgGbvFm4GBy+E=
=Jg3Q
-----END PGP SIGNATURE-----