Different table prefix for one table?

Question

Hypothetically, let's say I had multiple installations of some odd MySQL/PHP driven software. They are the same software so the database table structure is the same cross all of the installs. What I want to do, is make the software use a different database prefix for just one table. Specifically, a user table. So say the installs are set up like this:

• Main install: /home/www/main, database main, prefix is1
• Second install: /home/www/second, database main, prefix is2
• Third install: /home/www/third, database main, prefix is3

So what I want is to tell second install and third install to pull from the users table on prefix is1 for its own data via that table. Thus, any user that registers on main install is also registered on second and third and vice versa. Is it possible, and how would I do it if it is? Even if it's just a workaround that gives the same basic result I would be happy.

Answer 1

Your php code likely goes something like this in something like cfg.php:

$prefix = 'is3'

and in something like user.model.php:

$sql = 'SELECT * FROM `'.$prefix.'users`';

So you need to change in two of three installs code for working with 'users' table. But it seems to be too dangerous.

Answer 2

If you don't want to modify the app's PHP source-code, and it's not already configurable in this respect, then another option is to modify the database, changing is2users and is3users to be views on is1users:

DROP TABLE is2users;
CREATE VIEW is2users AS SELECT * FROM is1users;
DROP TABLE is3users;
CREATE VIEW is3users AS SELECT * FROM is1users;

(See http://dev.mysql.com/doc/refman/5.0/en/views.html for general information on views, http://dev.mysql.com/doc/refman/5.0/en/create-view.html for information on CREATE VIEW specifically.)

Depending on the app, this may not work perfectly -- for example, the app might cache some information in memory (such as the current value of an identifier-sequence) -- but it will probably work. Test it before putting it in production!

Answer 3

The setup of this is easy, schema-wise. You mention 'installs' which means you are using some packaged library which probably contains a config file where you can change various settings, and chances are one of the settings is the table prefix. If there is no table prefix option you can browse the install code and find where the schema is located and change the prefix manually for each install.

Now comes the hardpart, getting the code to behave as you described. You will have to make your app aware of all three databases, meaning you will probably have to add two new database connectors to the code (one connector for each database). Then you will have to add logic to handle user CRUD functionality to insert/update/delete user data across all three databases (transaction would be good here).

What you are describing is possible, but will require some custom code.