[zz]Multi Tenancy - The Physical Data Model

http://ayende.com/Blog/archive/2008/08/07/Multi-Tenancy--The-Physical-Data-Model.aspx

Continuing on the multi tenancy theme, let us talk about the actual physical structure of the data store. In general, you have two basic options.

The TenantId column and separated data stores. The TenantId is simply adding a column to all the columns, showing what tenant the row belongs to.
The separate data store calls for a much higher level of separation, where there is no shared storage for the tenants.
There are variation on those, such as having separation in the schema level instead of DB level, etc, but they all end up being down to one of those choices.

The TenantId approach allows you to use a single DB for the entire application, potentially reducing costs for managing the environment. It is also the easiest to explain, we just filter by TenantId and that is it!

It comes with a whole set of problems that is related to that decision, however:

Isolation - You have to actively do filtering all the time. This has major issues on your code. And now, tacking TenantId = @CurrentTenant at each query is not going to be sufficient. Consider the case of joins in such an environment, you are likely to get a filter only on the end result, after it was joined already. And that is just one example.
Backup - The backup strategy that you have for this scenario sucks. Basically, you have to roll your own.
Customer copy of the data - Going back to the backup strategy? You can't just hand the customer the backup. You have to dump the data out, and write something that writes it back in, and it has to be fast.
Security - you have to let all tenants access the data, security is in the app logic level.
Extensibility - in a shared environment it is hard to do a lot of extensibility in the data, because each client is stepping on each other toes.
As you probably gathered, I strongly suggest going with the separate model. Now our management strategy is dealing with the connection string. Isolation is built in and doesn't require any , for backup you have the vendor tools, and giving the customer its own data is trivial, just send him the backup file. Security is easy, a tenant can access only their DB, and extending this is much easier, because each tenant has their own walled garden to play with.

评论

1 楼 iunknown 2009-06-23  

http://discuss.joelonsoftware.com/default.asp?design.4.319460.16

Multi-tenant database design
We are in the process of designing an on-demand business application. We use MS SQL Server for the database.

Here are some of the constraints we've been given:
* Single database for multiple customers
* Customers can extend the data model by adding fields and tables (application is meta-data driven)

What would be the best way to solve this?

1. Have a TenantID field in all tables

The database remains static, i.e. adding a tenant does not change/add tables.

Extending the data model would not really change the physical model, but make use of extension tables. These extension tables contain the extra fields.

Advantages:
* Static data model
* App can use DB account with limited rights, always a good thing for security

Disadvantages:
* Complicated mapping between logical model the customer sees and the physical model, resulting in queries requiring joins to extension tables...
* Difficult to optimize the database, e.g. adding proper indexes on customer extensions

2. Have each table exist per tenant

Every table would exist once per tenant, e.g. the Account table would exist as Tenant1ID_Account and Tenant2ID_Account.

Customizations would really alter the data model.

Advantages:
* Simplifies queries and reduces the complexity of mapping
* Easier to optimize the database by adding indexes on some of the extensions

Disadvantages:
* Database account used by the application requires dbo rights, which may be dangerous if somebody finds a security hole letting them do a SQL injection attack
* Many tables. The application we are designing is a large suite of modules. This can easily add up to 500-1000 tables per tenant. A moderate success would result easily in a hundred tenants. I don't think managing a database with 100,000 tables is realistic