Laravel 5 Multi-Tenancy App with separate databases - users have access to multiple facilities

advertisements

Over the past couple of years I have developed a very customised PHP/MySQL application that is used for a number of clients. I have been creating a new database and new installation for each client up to this point.

The first obvious problem here is keeping multiple installations up to date with any code changes; a second problem is that each installation has a large amount of users; and for most clients; some of these users are the same - and they have to have a number of seperate user accounts and urls to remember.

I am moving the application over to Laravel 5 at the moment and looking into the best implementation for multi-tenancy; so looking for a little advice on the best implementation. I've used Laravel before but am by no means an expert.

This is what I am thinking as far as setup.

1 Master Database that holds tables for:

  1. All User account information
  2. Access Control Table - which installations the users can access; what their user level on that installation is.
  3. Configuration table for each installation - database connection info, basic configuration etc.

Then a seperate database for each installation that contains all the information that is needed for, and submitted to, that installation.

The ideal setup is that a user can go to a subdomain i.e installationname.appname.com; sign in with their master login details and automatically go to the required installation; OR go to appname.com, sign in and then select which installation to connect to.

My questions are:

  1. Is this the best arrangement to achieve what I am looking for.
  2. What's the best method for storing which installation the user is looking at (session variable)
  3. Can I define a model between 2 databases - perhaps define one connection as master connection, then dynamically define another connection using the database connection information in the master database to connect to the correct installation. - the system will often need to check the user info for access level etc.

I'm sure there's a lot of issues that I have not thought of; but if anyone has any links or guidance that may help that would be great. First time asking a question on SO but have found a huge amount of research help here in the past so thanks to the community!


UPDATE - So I think I have a way to make this work now; using seperate databases as above; set

protected $connection = 'tenant_connection'

in the models relating to tenant-specific database content.

Then somewhere in a header file set the tenant_connection that is wanted based on a session variable which has been set on login/by subdomain.

$tenant = Installation::where('installation', '=', $session['installation'])->first();
Config::set('database.connections.tenant_connection', array('driver' => 'mysql', 'host' => $tenant->db_hostname, 'username' => $tenant->db_username)... etc.

Assuming relationships will work across connections; I don't see why this would not work; just need to work out best place to set the tenant connection.


It is difficult to answer most of your question - as it is specific to your application and opinion based.

But the one bit I can answer is different models can have different database connections. So your user model uses the normal default connection - but your other models can use another connection:

class Example extends Model {

    protected $connection= 'second_db_connection';

}

Then in your DB connection file - you would have something like this:

return array(
    'connections' => array(
        'mysql' => array(
            'driver'    => 'mysql',
            'host'      => 'localhost',
            'database'  => 'database1',
            'username'  => 'user1',
            'password'  => 'pass1'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),

        'second_db_connection' => array(
            'driver'    => 'mysql',
            'host'      => 'localhost',
            'database'  => 'database2',
            'username'  => 'user2',
            'password'  => 'pass2'
            'charset'   => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix'    => '',
        ),
    ),