We have a great example application called LitwareHR that (amongst other things) demonstrates how to design a highly multi-tenant database – where the data of tenants exists in the same tables and SQL views are used to project out only the data for an individual tenant. However this is certainly not the right approach for all SaaS applications – hence I was interested to see this post on Zdnet:
"Certainly there are quite a few myths going around on the topic of multi-tenancy. The notion that any SaaS vendor worthy of the name must host every last one of its customers on a single application and database instance is clearly flawed. Even Salesforce.com, the self-proclaimed champion of multi-tenant SaaS architectures, is obliged to distribute its North American customer base across no less than five separate instances. The simple fact of the matter is that there are physical limits to how far you can scale a single multi-tenant instance using present-day technologies.
Other well-known SaaS vendors are even more equivocal in their actions (whatever their rhetoric). Read NetSuite’s S-1 filing and you’ll discover that, although smaller customers share multi-tenant instances, once an organization reaches 300-400 users, NetSuite will normally move it to a dedicated instance. Larger customers are allocated dedicated server clusters."
Interestingly Dynamics CRM 4.0 provisions a new database for each tenant.