Skip to content

WordPress 2.3 Taxonomy Schema

August 26, 2007
tags: ,
by Ryan Boren

WordPress 2.3 introduces our new taxonomy schema. This new schema replaces the categories, post2cat, and link2cat tables with three new tables that are more flexible. The first table is the terms table. It holds the basic information about a term.

 term_id bigint(20) NOT NULL auto_increment,
 name varchar(55) NOT NULL default '',
 slug varchar(200) NOT NULL default '',
 term_group bigint(10) NOT NULL default 0,
 PRIMARY KEY  (term_id),
 UNIQUE KEY slug (slug)

“name” is simply the name of the term. “slug” is the name reduced to a URL friendly form. “term_group” is a means of grouping together similar terms. “term_id” is a unique ID for the term.

A term is not a category or tag on its own. It must be given context via the term_taxonomy table.

 term_taxonomy_id bigint(20) NOT NULL auto_increment,
 term_id bigint(20) NOT NULL default 0,
 taxonomy varchar(32) NOT NULL default '',
 description longtext NOT NULL,
 parent bigint(20) NOT NULL default 0,
 count bigint(20) NOT NULL default 0,
 PRIMARY KEY  (term_taxonomy_id),
 UNIQUE KEY term_id_taxonomy (term_id,taxonomy)

The term_taxonomy table places a term within a taxonomy. This is what makes a term a category or tag (or both). “term_id” is the ID of a term in the terms table. “taxonomy” designates the taxonomy in which the term resides. The default taxonomies are “category”, “link_category”, and “post_tag”. “term_taxonomy_id” is a unique ID for the term+taxonomy pair. The rest of the fields provide information about the term in the context of the taxonomy. The “parent” field keeps track of hierarchical relationships between terms in the taxonomy. “description” provides a taxonomy specific description of the term. “count” tracks how many objects are associated with the term+taxonomy pair. Given a taxonomy of “category”, “count” tracks how many posts are in the category.

The final table, term_relationships, relates objects such as posts or links to a term_taxonomy_id from the term_taxonomy table.

 object_id bigint(20) NOT NULL default 0,
 term_taxonomy_id bigint(20) NOT NULL default 0,
 PRIMARY KEY  (object_id,term_taxonomy_id),
 KEY term_taxonomy_id (term_taxonomy_id)

“object_id” is the ID of a post or link. “term_taxonomy_id” is an ID from the term_taxonomy table designating a particular term+taxonomy pair.

The flexibility of the schema and API means plugins can add new taxonomies and object types quite easily. One of the Summer of Code projects does just that. The flexibility also allows us to easily retrieve all objects associated with a given term regardless of taxonomy, retrieve all terms from all taxonomies for a given object, and convert all categories to tags with one query.

The taxonomy schema is hidden behind a fairly comprehensive taxonomy API. The category API remains as a backward compatibility layer on top of the taxonomy API. Plugins that use the category API should not require any changes when upgrading to 2.3. Plugins that perform direct SQL queries on the categories, link2cat, or post2cat tables will break, unfortunately.

And that’s the new schema. It will break a few plugins in the short term, but in the long term it will allow us to add any new taxonomies that come along without needing to change the schema again.

from → WordPress
68 Comments
  1. Baris Unver permalink
    August 26, 2007 11:08 am

    Will there be an importer for UTW? UTW is an incredible plugin but I’d rather use the core instead of a plugin.

  2. Ryan permalink
    August 26, 2007 1:32 pm

    A UTW importer is bundled with WP 2.3.

  3. Baris Unver permalink
    August 26, 2007 1:57 pm

    Hooray!

  4. Dean permalink
    August 26, 2007 3:06 pm

    I’ve only recently switched to the “Simple Tagging” plugin because I’d had problems with UTW, I don’t suppose there will be an importer for that too?

    I guess if I need to I can just import from my UTW tags and then update the tags for the new posts I’ve created since switching.

    Other than that, the taxnomy schema looks pretty good I look forward to playing around with it.

  5. Ben Eastaugh permalink
    August 26, 2007 3:23 pm

    Baris, if you download the current trunk version of WP you can try the importer out now; just go to Manage > Import > Ultimate Tag Warrior. I tried it out today on the Tarski site and it worked perfectly.

  6. Darran permalink
    August 26, 2007 5:06 pm

    I am using Simple Tag for my blog, will WordPress 2.3 include an importer for it too? Because I would hate to have to re-tag everything again :(

  7. Connor Wilson permalink
    August 26, 2007 6:18 pm

    Awesome, I’m so glad it imports from UTW. I’ll be grabbing the public beta tomorrow for some bug squashing. ;)

  8. Jonathan Dingman permalink
    August 26, 2007 6:58 pm

    Will this new schema break a lot (most) of the plugins out there?

  9. GaMerZ permalink
    August 26, 2007 8:22 pm

    @Jonathan Dingman: Yes because the wp-admin got restructure. Personally I make use of wp-admin/upgrade-functions.php (WP 2.1+) but in WP 2.3 it is now wp-admin/includes/upgrade.php

  10. Paveo permalink
    August 26, 2007 8:46 pm

    Convert my tag system to WP 2.3 will be a big trouble, but I’ll take it too.

  11. Mr Papa permalink
    August 26, 2007 9:18 pm

    Any importer included for the simple-tagging plugin? as opposed to utw…

  12. darran permalink
    August 26, 2007 9:28 pm

    I am using simple tag in my blog. Will there be an import in WordPress 2.3 to handle simple tag too?

  13. GO permalink
    August 27, 2007 1:12 am

    What does UTW stand for?

  14. Ryan permalink
    August 27, 2007 7:08 am

    Someone submitted a Simple Tagging importer but we haven’t done anything with it yet. If anyone wants to try it out and give feedback, we can work it into 2.3.

  15. Micha permalink
    August 27, 2007 2:02 pm

    @Ryan: I have tested the Simple Tagging Importer with a nightly build of WordPress 2.3 and it works very well.

  16. Craig (mars-hill) permalink
    August 28, 2007 5:10 am

    @GO, UTW stands for Ultimate Tag Warrior; a popular WordPress plugin made by a great kiwi coder.

    http://neato.co.nz/ultimate-tag-warrior

  17. Andre SC permalink
    August 28, 2007 7:00 am

    Will classic UTW ( Go: UTW = UltimateTagWarior, a classic monster plugin) functionality like tagclouds and lists of related posts still require plugins or be part of the WordPress core?

  18. Jonathan Dingman permalink
    August 28, 2007 9:23 am

    Looks like I’ll have to be holding back from the 2.3 upgrade for a while then as a lot of my sites are plugin dependent :(

  19. Crow permalink
    August 28, 2007 10:10 am

    I’m not clear about the purpose of term_group. Isn’t that just a way to categorize the term? If so, doesn’t that belong in the taxonomy table?

  20. Businesslord permalink
    August 28, 2007 8:42 pm

    Hi. Will this new schema break a lot of the plugins out there? Thanks !

  21. Otto permalink
    September 6, 2007 2:05 pm

    Crow: term_group is used for aliasing. If you want to make two terms equivalent, then they go into the same group. This allows you to make two different tags that work together. Like I could create a tag for “coke” and “coca-cola” and when I pulled posts for either tag, I’d get both of them back.

  22. Soverato News permalink
    September 24, 2007 4:51 am

    How can i see in plugin code when they have a call to post2cat or link2cat?

Trackbacks

  1. Upcoming WordPress 2.3 will break current version of Organize Series Plugin « Unfolding Neurons
  2. WordPress 2.3 und die neue Tabellenstruktur | bueltge.de [by:ltge.de]
  3. Taxonomía en Wordpress 2.3 | aNieto2K
  4. A look inside the WordPress database « WordPress Bits
  5. Tip #5 : Taxonomy in WordPress 2.3 « WordPress Bits
  6. WordPress 2.3 entra nella fase finale
  7. Neue Datenbankstruktur in WordPress 2.3 - dynamicinternet
  8. You’ve Got Mail » Blog Archive » Ahhh…
  9. » Wordpress 2.3, rivoluzione in corso » Full(o)bloG
  10. WordPress 2.3, en un mes | Mangas Verdes
  11. WordPress 2.3 ‘will break a few plugins’
  12. evilzenscientist :: thoughts » WordPress 2.3 beta 1
  13. Asroone » اولین نسخه بتا وردپرس 2.3
  14. WordPress Francophone » WordPress 2.3 : Shéma de la taxinomie !
  15. WordPress 2.3 Beta 1 Review and Checklist » WPDesigner
  16. India60 » Wordpress 2.3 Beta 1
  17. IT Holics|What's Hot in Persian IT Blogs >> اولین نسخه بتا وردپرس ۲.۳ >> By Navid
  18. geek ramblings :: WordPress 2.3 Beta 1
  19. WordPress Wednesday News: WordPress Plugin Winners, 2.3 Beta, WordCamp in Beijing and Israel, WordPress.com Banned in Turkey, and More WordPress News : The Blog Herald
  20. Great Baron Unleashed! » Blog Archive » WordPress 2.3 Beta 1
  21. Wordpress 2.3 Beta 2
  22. WordPress 2.3 分类模式 | 我爱水煮鱼
  23. Transición de los estados de entradas en Wordpress 2.3 - Carrero Bitácora de los Hermanos Carrero, David Carrero Fernández-Baillo y Jaime Carrero Fernández-Baillo.
  24. Вградени тагове в WordPress 2.3 Beta
  25. 2007 Open Source CMS Nominees | iface thoughts
  26. Weblog Tools Collection » Blog Archive » WordPress 2.3 Features
  27. AK47 | new website henku.info » links for 2007-09-19
  28. Devlounge | Last Updated Categories WordPress 2.3 Query
  29. WordPressCHINA - WordPress中文化 » Blog Archive » WordPress 2.3 功能
  30. Wordpress 2.3: Saiba tudo antes de Atualizar | veines-noires.org
  31. Gormful » Breaks and Fixes
  32. Nuudelisoppa » Fixing my plugin’s SQL for Wordpress 2.3
  33. Wordpress 2.3 & K2 RC1 at EazyBlogga - Re-Discover the Web
  34. Wordpress 2.3 - Mögen die Spiele beginnen — Marnems Sicht der Dinge
  35. Upgrading to Wordpress 2.3 “Dexter” | info.michael-simons.eu
  36. Upgraded Wordpress 2.3!GeminiGeek’s Online Journal
  37. » WordPress 2.3 Struttura Tassonomia » WordPress Italy
  38. 塔塔@TaTa360.COM » Blog Archive » WordPress 2.3 功能
  39. Ärger mit WordPress 2.3 at blog :: weinschenker.name
  40. BlogBroker » WordPress 2.3 Features
  41. The KingOli Blog » Lost in Updates
  42. warpedvisions.org » Blog Archive » Listing categories using WordPress 2.3’s new taxonomy system
  43. T. Longren
  44. bmfh » Import von WordPress 2.3 nach Serendipity macht Schwierigkeiten
  45. WordPress 2.3 Tag News « Lorelle on WordPress
  46. Esquema taxonómico de WordPress — yukei.net

Comments are closed.