In SAAS architecture, how do I handle db schema and MVC user logins for multi-tenants

Question

Our requirement is something like this.

We are building a multi-tenant website in ASP.NET MVC, and each customer should be able to create their own users as per predefined user roles.

We are thinking about to create a schema for few tables which would be common for customers. So customer can login to system according to their schema logins and we need not to alter any queries to serve all of them.

We are referring http://msdn.microsoft.com/en-us/library/aa479086.aspx Shared Database, Separate Schemas.

Can someone suggest on following 1. After creating schema how to authorize user against a particular schema 2. Is this possible that without any changes in queries db can serve multi-tenants

Thanks in advance Anil

Answer 1

After much research, I can say that, although it takes more development up front and more checks along the way, shared database and shared schema is the way to go. It puts a little bit of limits on how easily you can cater to a client's specific needs, but from my point of view SAAS isn't about catering to a single client's weird needs. It's about catering to the majority of clients. Not that it's a SAAS but take iPhone as an example. It was built to cater to the masses. Rather than focusing on doing everything it's built to be one-size fits all just by its simplicity. This doesn't help your case when it comes to authoriztion but it'll save you dev hours in the long run.