Wordpress 2.3+ schema for taxonomy tables

I’ve been rewriting a number of my plugins to bring them up-to-date. This will include a version of If You Liked That that uses tags to identify similar or related posts. The new (version 2.3+) Wordpress taxonomy tables are not very well documented as far as I can tell so here is small EAR model that I’ve thrown together to describe the relevant sub-section of the overall Wordpress database schema.

Note: [term_taxonomy]![taxonomy] has values category or post_tag

Example code using these tables to select the taxonomy entries for post 45 is:

1
2
3
4
5
6

select p.post_title, r.term_taxonomy_id, t.taxonomy, te.name
from wp_posts p, wp_term_relationships r, wp_term_taxonomy t, wp_terms te
where p.id = 45
and p.id = r.object_id
and r.term_taxonomy_id = t.term_taxonomy_id
and t.term_id = te.term_id