Multiple wp_options tables to share content across installs

Question

My application has >10 user_roles, each able to perform completely different tasks, provided with an custom backend and no access to wp-admin.

The extra functionality each user_role gets to use is handled via multiple plugins. Each user_role interacts on the same data (posts, taxonomies etc.)

The idea I had is to use a single installation per one or more user_roles connected to a single database to share users, posts etc but with different plugins loaded, so simply a separate wp_options table.

The goal is to provide independent login areas(e.g subdomains, I don't want partners to use the same login as customers), easier routing (different permalinks and rewrites for each role) and less time checking for permissions of logged in users on protected sections of the site. Also I could disable themes for the 'backend-only' roles and only load the plugins each role needs to perform their actions.

My guess is, that a nice side effect would be a significant speed boost since the main site for customers is powered by woocommerce and I wouldn't need to activate woocommerce on other instances or vice versa... custom plugins would not be loaded in the installation powering the user/customer frontend.

I considered the multisite functionality but this wouldn't be the right path, since each blog has it's own posts table, which is not practical since I need to have all users use the same data.

Wordpress provides these constants to set up a custom users table, to share users among multiple installs:

define( 'CUSTOM_USER_TABLE', $table_prefix.'my_users' );
define( 'CUSTOM_USER_META_TABLE', $table_prefix.'my_usermeta' );

But it does not provide a method to define a custom options table, see this ticket https://core.trac.wordpress.org/ticket/7008

This thread provides a solution which is a bit of hacky but works fine in my initial test: https://wordpress.stackexchange.com/a/175494/68858

TL;DR: Modify wpdb-class by copying the functionality of CUSTOM_USER_TABLE to use CUSTOM_OPTION_TABLE in wp-config.php.

Btw. he states, content of the second site would link to the first, which is just an estimate I guess... so no, it does not, it works completely fine.

My question now is, do you see any kind of problems I may face with this approach in general? (many installations, one database, same database tables and an individual options table or each install)

Would you suggest a different approach or would you stay with the default wordpress-way of doing things?

What would be a solution to keep the 'hack' of modifying wpdb.php persistent in case of core updates?

Any other drawbacks besides maintainability? (which I think I gain a lot of by doing this)

When this project was stared almost half of the requirements didn't exist and wordpress+woocommerce was chosen due to simplicity. Using wordpress as a application framework is far more difficult and limiting, than I thought.

Thanks in advance for your help!

Update:

The question how to keep the modified version of wpdb in case of updates, or how to use custom table names besides CUSTOM_USER_TABLE was answered in a wordpress support ticket. The solution is to extend the wpdb-class, alter the table names in the tables function, save it as db.php to wp-content and use it as a drop-in. This way you can define custom names for all tables and avoid problems on updating core. (See: #33320)

Answer 1

Sounds like you are trying hard to shot yourself and you will likely succeed. Every framework has its own assumptions and limitation so if the assumptions of wordpress do not fit your needs then just don't use wordpress for the project.

If you (ok, probably the client) have decided that for reasons of cost, delivery time and maintainability wordpress should be used then you should strive to use the built-in wordpress tools instead of trying to go around them and re-inventing things just because if you were using framework X you would have done it that way. the more aligned you are with the wordpress core development practices the happier you will be (unless you are trying to ensure job security ;).

Sorry for the very meta answer but from your description it sounds like you don't want to do this project in wordpress although I didn't read here anything which might be problematic in wordpress and there is no need doing all the hacks that you think you should do.

Answer 2

This is a suggestion assuming the 3 following things:

• all sites are on the same database

• each sites is previously installed with its own base_prefix

• the following filter must be called as soon as possible, in our case using HyperDB and dropping the filter in db-config.php

    add_filter('query', function ($query){
  
            global $wpdb;
  
            $content_prefix = 'main_'; //prefic of the main installation where all posts and terms are located
  
            $query  = preg_replace('/' . $wpdb->base_prefix . '(?!options|users|usermeta)/i', $content_prefix, $query);
  
            return $query;
    });
  

It is also recommended to share the same UPLOADS path together with an offloading plugin for the media and synch the user table since the author info will need to be pulled on both sides.

If the data is located in different dabases/servers, you can keep the same filter and implement the HyperDB dataset methods.

Alternatively

It is also possible to use the dataset without filtering the query but you will need to use the same prefix in different databases.