使用PHP和MySQL进行多租户

Question

I have a web application built on CodeIgniter PHP, a MYSQL database, and we use PHPActiveRecords. We keep growing and expanding and now need to offer the application whitelabeled. I have most of that done the only problem I am running into is how to handle the database. I don't want to have two database connections because a good deal of the data between the two site will be shared. I was researched Multitenancy and it sounds like a great option, but if I have to go rewrite every ActiveRecord find to have a condition where tenant_id = 'this site' and then have to train my employees to do the same when they now write code, it isn't scalable. Does anyone have any ideas of how to either A) integrate multitenancy into PHPActiveRecords without a lot of modifications, or B) a better solution then multitenancy.

Thank you in advance.

Answer 1

Depending on how many clients you have, you could create a schema per client, on one host. Prefix the table names for your common tables with your common database names, and rely on the client queries to use the default database.

Your entrypoint may do something like:

$pdo->query('USE client_12345');

And your queries may be something like:

$pdo->query('SELECT * FROM clientspecificdata WHERE ...');

And

$pdo->query('SELECT * FROM common.data WHERE ...');

Note on this: this carries a relatively high risk of exposing data to the wrong clients. Be sure this is appropriate for your scenario. You may be much better off with multiple connections.