MultiTenancy application and structure

1

SaaS applications are on the rise, reducing costs, maintenance, etc.

We know we have the concept: MultiTenancy ( link )

They are:

By Column: Put an idClient column on all tables

By Schema: Separate clients by schema, client1.contactspay client2.contactspay client123234234.contacts etc.

By Bank: Each client with its database.

My question:

Separation by Column, tends to be the easiest, however, when seeing a DBA is very bad? if you have more than 130 tables and about 200 clients?

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

And performance? of queries, inserts, delete

Banks that will be created with this structure: Sql Server, MySql (Structure Generated with Entity Framework)

    
asked by anonymous 17.07.2014 / 21:28

1 answer

3

Separation by Column

DBA Point of View and Performance

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

Safety

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

How to solve?

There are a few ways:

  • All bank operations on Views; Views referencing only one table;
  • Handling events that involve context to include the client key whenever possible;

Entity Framework Viewpoint

Configuration is always more costly because all entities will have two or more keys. Scaffolding of the application becomes more complicated and ineffective. Operations tend to be slower because it is an extra column to be observed by the context.

    
17.07.2014 / 22:01