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 is the first-class table of the bunch (
wp_term_relationships). Terms are arbitrary. They are associated with taxonomies in the
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
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
wp_terms will change the name for each and every
term_id is associated with.