Add term_relationship_id column to wp_term_relationships

[johnjamesjacoby]

I have a need to attach additional information to the post/term relationships themselves. I'd prefer not to stash this data in postmeta or termmeta, as this data is strictly about the connection between the two, and not about either object.

Naturally, my solution for this is to create a metadata database table (like we did with comments years ago, and taxonomy terms recently.) The first of many steps towards a term_relationshipmeta database table is that wp_term_relationships lacks a term_relationship_id primary column in the database.

---

On the extreme end of this idea, probably lives a WP_Term_Relationship object, with methods for adding/editing/removing relationships, which the wp_*_object_terms functions would become wrappers for.

Before any of that is really feasible, or for very much work to continue even in private as a plugin first, term relationships need a unique identifier.

[johnjamesjacoby]

term relationships need a unique identifier

This probably deserves a correction. Term relationships are unique thanks to their primary key being a combination of the object_id and term_taxonomy_id. Unfortunately, the meta-data API (as it exists today) only knows how to work with single IDs, and also expects that ID to be an INT, so it's not even possible to hack together a creative solution.

If a wp_term_relationshipmeta database table were to exist today, it would need a dedicated meta-data API to accommodate its unique lack of a single primary integer key.

---

I'm going to attach a patch shortly to show my suggested schema changes for new installations. The necessary alters to upgrade this database table may come later, if this suggestion is sound.

[johnjamesjacoby]

38265.diff does the following:

• Changes the primary key to a new term_relationship_id column
• Maintains the uniqueness of the object_id and term_taxonomy_id column index

With these 2 changes, the following is achievable:

• Term relationships are now identifiable using 1 single unique integer
• wp_term_relationshipmeta can associate meta data to the penultimate term_relationship_id column using the meta-data API

---

Things to do:

• ALTER queries for upgrade routine
• Performance test whether object_id needs its own index, vs. the composite one on both