The Content Relations represent translated/connected/related content (e.g., posts, and terms). Currently, we store the according information in a single table. This table has an ID column (which is not really useful). Also, there are source blog ID and source content ID columns that are (mis)used as a pseudo coposite key. Then there's a self-reference (source blog/content ID equals target blog/content ID), lots of redundancy, and complicated and inefficient queries.

So, the whole Content Relations infrastructure will be refactored.

We will use the following two (as-good-as-can-be normalized) tables to handle content relationships:

{$table_prefix}mlp_relationships

This table will handle the individual relation groups (so-called relationships). Since a post can only be related to another post (and not a term, for instance), the type will be integrated into the relationship itself (and not into a related content entry, see next table).

The (auto-incremented, hence unique) id column will be used as primary key.

{$table_prefix}mlp_content_relations

This table handles the individual content elements.

The unique combination (relationship_id,site_id,content_id) will serve as primary key, and, for efficient querying, we will also add the composite key (site_id,content_id).

Why exactly those keys, you ask? Please read this thread on the DBA StackExchange.

The refactoring will be performed as follows:

• Refactor the database-related classes:
  • Refactor Mlp_Content_Relations, Mlp_Content_Relations_Interface and Mlp_Content_Relations_Schema.
  • Introduce Mlp_Relationship_Schema.
• Adapt the classes/methods that directly work with the database:
  • Adapt feature.duplicate-blog.php;
  • Adapt Mlp_Duplicate_Blogs.
• Adapt the classes/methods that explicitly use the according database API:
  • Adapt Mlp_Language_Api.
  • Adapt Mlp_Translatable_Post_Data.
  • Adapt Mlp_Relationship_Changer.
  • Adapt Mlp_Term_Connector.
  • Adapt Mlp_Term_Translation_Presenter.
• Adapt everything that is implicitly using the databse API (i.e., using one of the above methods, and all other levels down the call stack):
  • Adapt Mlp_Helpers.
  • Adapt Mlp_Update_Plugin_Data.
  • Adapt Mlp_Relationship_Control.
  • Adapt Mlp_Relationship_Control_Ajax_Search.
  • Adapt Mlp_Relationship_Control_Meta_Box_View.
  • Adapt Mlp_Term_Translation_Selector.
• Thorougly test the new infrastructure (using a fresh MLP setup).
• Implement all that's necessary for the (non-intrusive) data migration.
  • Delete invalid content relations in the old (!) multilingual_linked table.
  • tbc.
• Adapt MultilingualPress front controller.
• Adapt uninstall.php.

During or after the refactoring, we will also tackle all open Content Relations issues.

One of the things that are left open for now is how far we will go regarding backwards compatibility. When the time is right, we will discuss this.