[Gerhard Liebenberg]

Hi guys

I'm developing a commercial SaaS site with a shared DB. However, I am participating problems with a shared DB and consider moving towards a separate DB for each tenant.

Reasons:
1. Commercial clients never want exactly the same system, simply because their business-processes are unique. Separate DB could make individual tweaks easier.

2. Commercial programs are not simple. They normally have many things that the user could do wrong. Restoring backups to a shared DB could influence innocent tenants. Trying to correct user mistakes in only certain records could be dangerous for other tenants if you are not careful - and normally you don't have much time for testing, because the client's business must go on.

3. Most tenants want their invoice numbers to be sequential. How do you do that in a shared DB? Auto-increment will not work, because tenants share the invoice table, meaning tenant-A will have invoices 1, 5, 13, 87,,, That will not work.

4. Separate DB's could be much faster and scaling is a dream.

5. Some clients insists on their data being in a separate DB.

Negatives:
1. Separate DB's are more expensive. Really? Is this still a factor to mention? I mean hosting at world-class institutions now cost as low as US$2 per month per database.

Question:
So I want a main DB which contains all the tenants. Then I want to use the tenant_id as the name for the tenant's own database. The connection string must therefore use the tenant_id (stored in session) to connect with the tenant's own DB.

Any thoughts? Any hiccups?

[Roman Solomatin]

I would choose separate DBs also.

However, You may need to implement your own CDbConnection since connectionString property seems to be read-only. This new class needs to construct the proper connectionString to connect to tenant's DB.

[waitforit]

I'm going to push back on your reasons a little bit. I have a little experience in this area but I appreciate the discussion as it stimulates some ideas for my projects as well.

1. SaaS software is typically very opinionated. Any flexibility in schema has to be reflected in code, right? If this is really a SaaS app you will have identical code for all clients - how do you plan to support flexibility without writing custom code per customer? Because if you are doing that I don't believe you have a SaaS application anymore.

2. Yes and no. It truly is more complex to deal with DB backups / restorations in this scenario but since each record is keyed to a client it is easy to only update records matching a clientID.

3. I would use the ID key as a non-client specific record (or forego one, entirely). Consider this.. you have an invoiceID and tenantID field in your invoice table. The primary key for the table can be composite of both keys. You can either get the max() value per tenant or try this link for an idea using a DB trigger:
http://stackoverflow...nt-both-columns

4. Could be. Or could not be, depends on design. Most databases handle millions of records well with proper indexes. The exception may be if you have really 'wide' tables (lots and lots of columns).

5. Again, for a SaaS app (my experience) the client usually has stipulations based on data integrity and security. They don't really specify how you achieve that, but that may be the case in your area.

There are pros and cons. One option you didn't mention is unique schema or views per client instead of dedicated DBs. This would be a hybrid approach.

More food for thought:

http://stackoverflow...-in-every-table

[seenivasan]

Dear Gerhard Liebenberg

Quote

Question:
So I want a main DB which contains all the tenants. Then I want to use the tenant_id as the name for the tenant's own database. The connection string must therefore use the tenant_id (stored in session) to connect with the tenant's own DB.

I have no idea about Saas.

I once succeeded in switching between databases depending on the values stored in session.

I hope we can apply that in your scenario.

If user has right to have separate database, when he gets logged in we are storing his id as database name.
SiteController.php

public function actionLogin()
	{	
	    $model=new LoginForm;
	    if(isset($_POST['LoginForm']))
	    {
		$model->attributes=$_POST['LoginForm'];

		if($model->validate() && $model->login())  {

                        $user=User::model()->findByAttributes(array('username'=>Yii::app()->user->id));

                        if($user->hasOwnDatabase)
                             Yii::app()->user->setState('database',Yii::app()->user->id);

			$this->redirect(Yii::app()->user->returnUrl);  }
	    }
		
		$this->render('login',array('model'=>$model));
	}

Create an application behavior and attach it to main application.

applicationBehavior.php in components directory

<?php
class applicationBehavior extends CBehavior
{      
    private $_owner;
    public function events() {

                        return  array(
                                'onBeginRequest'=>'assignDb',        
                        );
                             }
        
    public function assignDb()
    {
        $owner=$this->_owner=$this->getOwner();
                
        if(!$owner->user->getIsGuest && $owner->user->hasState('database'))
        {
            $database=Yii::app()->user->getState('database');                
            $owner->db->setActive(FALSE);   

$owner->setComponent('db',new CDbConnection("mysql:host=localhost;dbname=$database",'root','yourpassword'));
                                                   
            $owner->db->setActive(TRUE);
                                
        }
    }
        
}
?>

Attach this behavior instance as a property to main configuration file. Leave the 'db' component intact.

return array(
......................................
.......................................
'behaviors'=>array(
'class'=>'application.components.applicationBehavior',
),
......................................
......................................
'components'=>array(
.........................................
........................................
'db'=>array(
                        'connectionString' => 'mysql:host=localhost;dbname=routine',//routine-default database
                        'emulatePrepare' => true,
                        'username' => 'root',
                        'password' => 'password',
                        'charset' => 'utf8',
                ),
.....................................................
....................................................
),
)

So when user is created, if he has right to have separate database it is stored as a boolean. Then we create a separate database on his name. After successful registration, when he login, he will use his own database on the next immediate request. Otherwise he will work on default database.

I do not know exactly what you expect.

I hope this helps a bit.

Regards.

[Gerhard Liebenberg]

Hi guys, thanx for the nice feedback. This is what saves people lots of time and struggling.

Roman Solomatin:
Thank you for the tip. I will check it out.

waitforit:
I see what you mean: all of my concerns could be addressed in a single DB as well. And I would actually prefer to continue using a single DB (much easier and much less maintenance work).

But as you said, I guess it boils down to how much "custom code per customer" are you going to allow - regardless of whether the final product is truly SaaS or not (none of my clients would care anyway).

I don't plan on much customisation. I would rather want to add new program-functions as customers come with new requests. Then all other customers could decide for themselves what functions they want to use.

But, I'm very scared of having to tell a customer - maybe five years from now - "sorry Sir, we can't add your patients' scanned x-rays to the database, because that will slow down all our other customers", or "sorry Sir, we can't move your data to a faster server that is closer to your home, because it is kept in a database closer to most other customers in the Kalahari".

I've never done this; so maybe my concerns are ridiculous. I'm just trying to avoid future surprises. I hate surprises:)

But you're making a good point.

Could you please elaborate on "unique schema or views per client instead of dedicated DBs". I know SQLServer uses schemas inside databases, but it looks like a schema and a database is the same thing in MySQL.

seenivasan:
Since you are already doing this stuff, I really value your input. I got more than what I expected.

Many thanx.

[CodeButterfly]

Check out this IBM article:

Convert your web application to a multi-tenant SaaS solution

Quote

Of course the most radical method (for a SaaS application at least) to use when the database capacity cannot meet the demands is to establish a separate database. Anyone who wants a multi-tenant SaaS application has to consider that this option can lead to the untenable situation of supporting a database per tenant, which leads directly to the type of inefficiencies multi-tenancy strives to avoid.

Another article:

Multi-Tenant Data Architecture

[Gerhard Liebenberg]

Hi CodeButterfly

I guess you win some and you loose some. Multiple databases will make life easier in some situations but in others not.

So each developer needs to evaluate his/her own environment. My clients are very specialized medical training institutions. So even if each tenant has its own database, there will probably not be that many. And as long as you keep all schemata identical, it should not be that hard to manage.

And I do agree with the article in that: multiple databases should not result in unique programming features for different tenants. That will be a nightmare. But what I am willing to do is to write different add-on functions, which all tenants can choose to use or not. If your database design is done correctly, this should not be a problem.

[Gerhard Liebenberg]

Okay, I think I will try to have best of both worlds:

I first wanted to have a main db with a table containing all the tenants. Then I have a separate db for each tenant with their tenant-id forming part of their db's name.

Now, I think I will change it to this:
I still have a main db with a table containing all the tenants.

Then I have a single secondary db which stores all the tenants' data. This secondary db thus contains multiple tenants. IF a tenant needs to be moved to its own db, then I create another secondary db for that tenant.

The main db will store the name of the secondary db to use for each tenant.

So each secondary db can have just one or multiple tenants.

This way, you can keep all tenants in one db, until you need the ability to separate some of them. You also have full control over the number of secondary databases created.