WordPress 2.3 first introduced the current taxonomy schema. Any plugin that queries against the old WordPress 2.2 table structure will break horribly; plugins that use the category API should be fine.
This new schema replaced the categories, post2cat, and link2cat tables with three new tables that are much more flexible.
The first table is the terms table. It holds the basic information about single terms.
term_id bigint(20) unsigned NOT NULL auto_increment, name varchar(200) 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), KEY name (name)
term_idis a unique ID for the term.
nameis simply the name of the term.
slugis unique and is the name reduced to a URL friendly form.
term_groupis a means of grouping together similar terms.
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) unsigned NOT NULL auto_increment, term_id bigint(20) unsigned NOT NULL default 0, taxonomy varchar(32) NOT NULL default '', description longtext NOT NULL, parent bigint(20) unsigned NOT NULL default 0, count bigint(20) NOT NULL default 0, PRIMARY KEY (term_taxonomy_id), UNIQUE KEY term_id_taxonomy (term_id,taxonomy), KEY taxonomy (taxonomy)
The term_taxonomy table places a term within a taxonomy. This is what makes a term a category, a tag or part of a custom taxonomy (or in a combination of taxonomies).
term_idis the ID of a term in the terms table.
taxonomydesignates the taxonomy in which the term resides. The default taxonomies are
term_taxonomy_idis a unique ID for the term+taxonomy pair.
The rest of the fields provide information about the term in the context of the taxonomy.
parentfield keeps track of hierarchical relationships between terms in the taxonomy.
descriptionprovides a taxonomy specific description of the term.
counttracks how many objects are associated with the term+taxonomy pair. For example, given a term of the
counttracks how many posts are in that specific 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) unsigned NOT NULL default 0, term_taxonomy_id bigint(20) unsigned NOT NULL default 0, term_order int(11) NOT NULL default 0, PRIMARY KEY (object_id,term_taxonomy_id), KEY term_taxonomy_id (term_taxonomy_id)
object_idis the ID of a post or link.
term_taxonomy_idis an ID from the term_taxonomy table designating a particular term+taxonomy pair.
term_orderallows an ordering of terms for an object (see ticket #5857)