Multitenancy application and structure

Question

Saas applications are getting higher, reducing costs, maintenance, etc.

We know we have the concept: Multitenancy (http://msdn.microsoft.com/en-us/library/aa479086.aspx)

It’s them:

By Column: Place an idClient column in all tables

By Schema: Separating clients by schema, cliente1.contaspagar cliente2.contaspagar cliente123234234.contaspagar etc

By Database: Each customer with their database.

My doubt:

The separation by Column, tends to be the easiest, however, when viewing from a DBA is very bad? if it has more than 130 tables and about 200 clients?

What about security? Remembering that what separates the data of each client, is a simple "Where" Where idclient = ? Or how that security could be made?

And performance? of queries, Inserts,delete

Databases that will be created with this structure: Sql Server, Mysql (Structure generated with Entity Framework)

Answer 1

Column separation

DBA View and Performance

The amendment will always require a composite index, which is larger and slower than the index based on only one column.

Security

It is the most insecure separation of all, no doubt, because all operations of selection and handling of records are separated by a conditional.

How to solve?

There are some ways:

• All bank operations on top of Views; Views referencing only one table;
• Manipulating context-related events to include the customer key where possible;

Entity Framework Point of View

The configuration becomes more expensive, because all entities will have two or more keys. The application’s Scaffold becomes more complicated and ineffective. Operations tend to be slower because it is an extra column to be observed by the context.