Multi-tenancy - Create tables up-front or as needed?

Question

Hi,

I'm currently working on a SaaS type application, and for multi-tenancy I've settled on one database per user, with each database containing all tables required by the functionality the user is entitled to (have payed for). The application is designed to capture data (i.e. like web analytics) and present it for the user. The design should be able to scale to the tens of thousands of users (eventually).

Would a viable solution be to create the tables 'dynamically' when the application detects they are required? Or should I create all eventually required tables in specific user databases, as soon as I know they may be needed (user upgrade, new features, etc.)?

My current architecture would allow me to do something like this:

function insertData($table, $data) {
    mysql_query("INSERT INTO ".$table." VALUES ('".implode("', '", $data)."')");
    if ( mysql_errno() ) {
      if (mysql_errno() == 1146) { // table does not exist
        if ( $this->createTable($table) ) {
          $this->insertData($table, $data)
        }
      } else {
        // other errors
      }
    }
}

I would like the flexibility to be able to add functionality without having to loop through all user databases to add tables, so a setup like the above would help me achive that. But I'm not sure if I'm missing something that would make me regret the decision later?

Answer 1

Using an insert to test whether a table exists may not be your best bet, particularly considering the size of the row and latency to the database server. A better solution might be to use 'show tables', i.e.:

SHOW TABLES LIKE 'TABLENAME';

The resultset will be something like this:

Tables_in_DATABASENAME (TABLENAME)
----------------------------------
be_groups

If the number of rows is zero, clearly the table does not exist.

As far as your process is concerned -- I think I'd take a hybrid approach. When the customer upgrades and as part of your provisioning workflow create all the tables needed to provide the new services to the customer. That way the customer doesn't take up any more resources (disk mainly, in this case) than they're paying for, and you're not causing yourself the pain of provisioning dynamically.