PHP/MySQL - Working with two databases, one shared and one local to an instance of application

Question

The situation: Using a off-the-shelf PHP application, I have to add in a new module for extra functionality. Today, it is made known that eventually four different instances of the application are to be deployed, but the data from the new functionality is to be shared among those 4 instances. Each instance should still have their own database for users, content and etc.

So the data for the new functionality goes into a 'shared' database. The data for the application (user login, content, uploads) go into a 'local' database

To make things more complex, the new module I am writing will fetch data from the local DB and the shared DB at the same time.

A re-write of the base application will take too long. I only have control over the new module which I am writing.

The ideal solution: Is there a way to encapsulate 2 databases into one name using MySQL? I do not wish to switch DB connections or specifically name the DB to query from inside my SQL statements.

The application uses a DB wrapper, so I am able to change it somehow so I can invisibly attempt to read/write to two different DB.

What is the best way to handle this problem?

Answer 1

I do not wish to switch DB connections or specifically name the DB to query from inside my SQL statements.

What's wrong with that? If the application DB wrapper is "object-oriented" (meaning it keeps the connection handle and you could have two or more independent instances of the DB wrapper, even if that means two connections) it seems to me that the easiest and most straight forward solution is just to have two DB wrapper instances. Use the "default" one for the local database that the application knows and uses and another instance for your shared database. Adding the extra database parameter when doing queries also looks ok to me.

How would you encapsulate two databases into one? How would the hypothetical middle-layer know which queries should go to which database? Even if you modify the DB wrapper, how do you imagine it will figure which database is the right one? Like, having a list of tables names to query for db1 and a list of tables to query db2... Nah, introducing some arcane rules will get messy soon.

Answer 2

Thinking through it, I came up with an idea myself. The 'shared' database is acting more like a service to the application. So instead of retrieving data directly from it, I will encapsulate it away as a service and use XML-RPC to retrieve the data.