I meant to include this in my MySQL post, but making a separate post to address this one particular issue works just fine. In my previous post, I said that the Term tables are a mess. Here is why:

Let’s say I create 2 taxonomies, location and radio_station (FYI: an example of a “taxonomy” is a “tag” or a “category”). Here’s how I register them (simplified) in my theme or plugin:

<?php
register_taxonomy( 'radio_station', 'post', array( 'labels' => array(
    'name'          => 'Radio Stations',
    'singluar_name' => 'Radio Station'
) ) );
register_taxonomy( 'location', 'post', array( 'labels' => array(
    'name'          => 'Locations',
    'singluar_name' => 'Location'
) ) );

Once this is done, you should have extra links in the Posts fly-out:

Click each link (Tags, Radio Stations, Locations) and add “Brooklyn” as a term. This is a completely valid practice. Now change ONE of them to “Brooklyn’s Finest” and go back and check out the other 2. THEY ALL CHANGED! So that sucks right?

Why does this happen? Terms are stored in wp_terms and are constrained by uniqueness. So when you added Brooklyn that second time – even though it had a different taxonomy – it pointed at that first term, and more importantly, shackled itself to it forever by associating it with that first term’s term_id in the wp_term_taxonomy table.

wp_term_taxonomy is the first-class table of the bunch (wp_terms, wp_term_taxonomy, and wp_term_relationships). Terms are arbitrary. They are associated with taxonomies in the wp_term_taxonomy table. term_taxonomy_id is the PRIMARY KEY, and term_id is the foreign reference. Weirdly, taxonomy is not a foreign reference alluding to wp_taxonomy, there is no wp_taxonomy table! wp_term_relationships joins term_taxonomy_id with Post IDs.

So let’s say you have 1,000,000 terms but only 5 taxonomies. If 90,000 of them are post_tags, the taxonomy field for all of them will be post_tag. The opposite is not true. 5 taxonomies only point at ONE Brooklyn. In this scenario, shared terms make no sense. Changing a term’s name that that is stored at term_id in wp_terms will change the name for each and every taxonomy that term_id is associated with.

Discuss.