What is the difference between term_id and term_taxonomy_id

Because this is part of a greater understanding of the design, I will describe it as a whole … 🙂

In WP 4.5.3 there are still all these tables (I will talk about it without prefix):

• post
• Term_relationships
• term_taxonomy
• conditions

The path to get post terms’ readable names is all due to them all.

post

The most important identifier here is ID – an ID of a message (of any kind)

Term_relationships

pairs of:

object_id – can are posts.ID (but does not must be)

term_taxonomy_id – This is NOT Id of a term (category) but one ID of RELATION between a term (category) and taxonomy (“category type”)

term_taxonomy

The most important identifier here is term_taxonomy_id Desribed above ^^

Another important columns:

term_id – An ID of a term (category)

taxonomy – the term taxonomy stores (“category type”)

This one may seem funny, but the first was planning to add the possibility to terms to have more taxonomies (in that in some cases can be useful).

conditions

The most important identificator here is the term_id – an ID of a category

Another important columns here are:

name – readable category name eg “Music genres”

slug – the snail of a usable term, eg in URL

So the cheeky Demonstrate SQL

Get all published messages and all their categories with names of categories

Could look like this (add prefixes to tables when testing on your own WP DB):

SELECT * FROM
posts #gets posts
LEFT JOIN
term_relationships #gets posts relationships to term_taxonomies
ON(posts.ID=term_relationships.object_id)
LEFT JOIN
term_taxonomy #gets term_ids 
ON(term_relationships.term_taxonomy_id=term_taxonomy.term_taxonomy_id)
LEFT JOIN
terms #finally, gets terms' names
ON(term_taxonomy.term_id=terms.term_id)
WHERE (
    (posts.post_status="publish") 
    #optionally you can filter by a certain post_type:
    #AND
    #(posts.post_type="some_post_type")
)
ORDER BY posts.ID ASC