Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

multi-tenant database architecture [closed]

I am building a SAAS application and we are discussing about one database per client vs shared databases. I have read a lot, incluisve some topics here at SO but I have still many doubts.

Our plataform should be highly customizable by each client. (they should be able to have custom tables and add custom fields to existing tables). The multiple database aproach seems great in this case.

The problem is. should my "users" table be in the master database or in each client database?. A user might have one or more organizations, so it would be present in multiple databases. Also, what about generic tables like countries table, etc?

It makes sense to be in the master database. But I have many tables with a created_by field which have a foreign key to the user. Also have some permission related tables by client.

I would loose the power of foreign keys if multiple databases, which means more queries to the database. I know I can use cross-join between databases if they are in the same server but then i loose scalability. (I might need to have multiple database servers in future). I have tought about federated tables. Not sure about performance.

The technologies I am using are php and symfony 2 framework and mysql for the database.

Also, I am afraid about the maintenance of such a system. We could create some scripts to automate the schema changes in all databases, but if we have 10k clients that would mean 10k databases.

What is your opiniion about this? The main caracteristic of my app should be flexibility so if a client needs something more specific than the base plataform doesnt have, it should be possible to do it for him.

like image 544
brpaz Avatar asked Jan 28 '13 18:01

brpaz


People also ask

What is multi-tenant database architecture?

The simplest multi-tenant database pattern uses a single database to host data for all tenants. As more tenants are added, the database is scaled up with more storage and compute resources. This scale up might be all that is needed, although there is always an ultimate scale limit.

What are the three multi-tenancy models?

There are three multi-tenancy models: Database, Schema, and Table. In Database multi-tenancy, the application connects to a database and gets data while the tenancy logic is delegated to the ops layer.

What is multitenant architecture difference between Singletenant and multi-tenant?

In a single-tenant cloud, each customer lives alone in a single apartment building which has its own security system and facilities and is completely isolated from neighboring buildings. In multi-tenant cloud architecture, tenants live in different apartments inside a single apartment building.

What is multi-tenant architecture example?

Some multi-tenant architecture examples would be Hubspot, Github, and Salesforce. In each case, every user shares the main multi-tenant database and software application, but each tenant's data is invisible to others and isolated.


1 Answers

Some classic problems here. Have you ever been to http://highscalability.com/? Some good case studies there.

From personal experience if you try to share clients on one server, you will find that a very successful/active user will take up all the resources of the machine over time. We had one client in a SAAS that destroyed a shared server and we had to move him somewhere else.

I would rip out global enumerations into a service. You can make one central database for things like list of countries, list of states, etc. and put it behind a web service layer. Also in that database you can have user management/managing what server belongs to what user etc. You can make a management portal that reads/writes to this database for managing your user base.

If I was doing a SAAS again, I would start small and wait for the pain to hit. What you really want are good tools to address the scaling issues when they happen. Have some scripts ready to do rolling schema changes across servers (no way to avoid this once you have more than one server). Have scripts to take down machines while you are modifying the schema. Have scripts to migrate a user from a shared server to a dedicated one.

Consider setting up replication from a central database. This would pump down global information that each user partition/database would need without you having to write a lot of code.

But the biggest piece of advice I've seen - and experienced first hand - don't try too hard to build the next Facebook for scale. Start simple and see what actually happens before worrying about major scalability issues. You might be surprised as the user base grows what scales well and what does not.

like image 177
ryan1234 Avatar answered Nov 15 '22 19:11

ryan1234