Database Structure on Different Servers

Question

I'm building a website on Wordpress that requires users to register/login in order to use all the website functionalities. The functionalities are to create a wishlist of products, to compare among products in your wishlist, and to request info on a specific product.

However we were thinking of using a plugin for the registration part. The problem we are trying to avoid is adding complexity when it comes to relating the wishlist and info request to the users.

The thing is there's a possibility (we are still researching) that the users could be stored in a different server from the other functionalities.

If that were the case, is that going to generate problems when launching the functionalities and perhaps more importantly is it going to create data problems in the future.

Answer 1

I don't think Wordpress can manage two databases at the same time (unless you tweak it significantly, by means of HyperDB¹). So, you'd probably will like the job of abstracting two databases and making them look like one to WordPress to be done by the database functionality.

WordPress can work mostly with MySQL (see WordPress Requirements).

So, your only option is for MySQL to proxy your users table to another MySQL server. This can actually be done.

From the documentation of MySQL's FEDERATED Storage Engine

The FEDERATED storage engine lets you access data from a remote MySQL database without using replication or cluster technology. Querying a local FEDERATED table automatically pulls the data from the remote (federated) tables. No data is stored on the local tables.

So, in theory², you could have your users in a different server than the rest of the data, and still let WordPress work (transparently).

---

If you're using PostgreSQL, (via PostgreSQL for WordPress (PG4WP)³, for instance) you can do the same thing by means of a FOREIGN DATA WRAPPER. A foreign data wrapper (FDW) can connect to either another PostgreSQL server or other types of database servers.

From PostgreSQL's wiki page about Foreign data wrappers

In 2003, a new specification called SQL/MED ("SQL Management of External Data") was added to the SQL standard. It is a standardized way of handling access to remote objects from SQL databases. In 2011, PostgreSQL 9.1 was released with read-only support of this standard, and in 2013 write support was added with PostgreSQL 9.3.

There are now a variety of Foreign Data Wrappers (FDW) available which enable PostgreSQL Server to different remote data stores, ranging from other SQL databases through to flat file.

FDW are based on the standard SQL/MED.

Check also CREATE FOREIGN DATA WRAPPER and postgres_fdw, for PostgreSQL to PostgreSQL connections.

---

¹_{The fact that HyperDB hasn't been updated for more than 2 years isn't encouraging.}
²_{I've never tried it.}
³_{Three years without updates... again not encouraging}