What are the best practices in building multi-tenancy applications?

Question

What are the best practices in building applications that support multiple tenants such as Software as a Service?

Links to white papers that expand on this topic are greatly appreciated.

Isleana

Answer 1

You might find some valuable advise in a series of blog posts by Oren Eini.

This is one of the last posts in the series, with links to previous posts: http://ayende.com/Blog/archive/2008/08/16/Multi-Tenancy--Approaches-and-Applicability.aspx

Isley

Nice tutorial.Thanks for the link. – Darien

Answer 2

For the database:

A. Put everything on the same database, put a tenant_id column on your tables

Pros: Easy to do

Cons: Very prone to bugs: it's easy to leak data from one tenant to another.

B. Put everything on the same database, but put each tenant in its own namespace (postgresql calls them schemas)

Pros: Provides better data leak protection than option A

Cons: Not supported by all databases. AFAIK PostgreSQL and Oracle supports it.

C. Setup one database per tenant

Pros: Absolutely no chance of data leaking from one tenant to another

Cons: Setting up new tenants is more complicated. Database connections are expensive.

I only learned the above ideas from Guy Naor. Here's a link to his presentation: http://aac2009.confreaks.com/06-feb-2009-14-30-writing-multi-tenant-applications-in-rails-guy-naor.html

Zacharyzacherie

In #B, all databases support schemas, but with different terminology. In MySQL, schema and database are synonymous. MSSQL also has schemas support now. Our multitenancy app in production runs with ~4000 (as of now) databases/schemas on MySQL. – Unionism