How to work with multi tenancy architecture?

Question

I was asked to develop an application with multi tenancy architecture in the PHP language using Mysql as a database.

After reading several materials and posts on the Internet, I had a question. How to model the application itself?

I have two options:

• Shared application and database
  In this case, it would have only one application and database instance for all clients.
• Shared application and isolated databases
  In this case, you would have only one instance of the application and each customer would have their own database.

The application will cater to companies (beauty salons) that may have only the head office and/or the head office and branch units. All two types will be multi-users and several modules, some of them: Customer Registration, Supplier Registration, Professional Registration, Product and Service Registration, Calendar, Stock Control, Financial, Sales and Purchases.

Who has or has had experience with this type of multi-tenancy architecture, what do you have to advise me regarding the use of the database? Because the question is: Use the same database for all customers or set up a new database for each new customer?

Answer 1

I recently worked on a "multi-tenant" project. I did not participate in the decision of the architecture; I only did maintenance and development with the project in advanced stage.

There was a "central" database, which even had the "tenants" register in it. And, for each "tenant", among the procedures at the time of its inclusion, a new database was created.

The architecture seemed very good, and the code was also well structured, very coherent, without confusion. From this brief interaction with this system, I would not recommend using the same database for all customers. Separating "one database for each customer" makes all application development much simpler.

Each case is a case, but it is suggested from the contact with a real application: a "central" BD and a BD for each client is a good request.

P.S. - Of course the tools you have and your experience with them will be important to manage this complexity. In this case, the application was in Symfony 2 (that I particularly dislike), but I imagine the same architecture held in Laravel 4 (that I particularly liking) without great difficulty.

Answer 2

Using the same database for all customers or building a new database for each new customer?

What You Should Analyze:

1 - How many potential users you expect to reach ?

It’s hard to estimate the amount with authority, but I think which in terms of orders of magnitude would be: Thousands? tens of thousands? more? If it is a high number of users it would be advisable to use the shared approach.

2 - How much storage space you expect from average users to occupy?

If you expect that these users will have to store large amounts of data the separate database approach is probably the best.

3 - How many concurrent users do you expect to support ?

The higher the number, the more suitable the insulation would be to meet the requirements.

4 - You expect to offer any value-added per-tenant services such as per-tenant backup and restore backup?

Such services are easier to offer through a more isolated approach.

Of course with the growth of some technologies the support/ storage of information can be easily questioned, but in short this is it!

Analyzing your problem:

The application will cater to companies (beauty salons) that may have only

the head office and/or the head office and the subsidiary units. All two types will be multi-users and several modules, some being: Customer Registration, Supplier Registration, Customer Registration professional, Product and Service Registration, Agenda, Control Stock, Finance, Sales & Purchases.

If you have understood all that I have explained above your doubts will be as follows::

Companies will ask for additional service as backup or have several other features that do not make sense to have in another salon (Concerning companies that sell a Service and customizations for each customer.)?

Then it will be recommended to isolate the database, otherwise prefer the use of a shared database.

Reference: How to create a multi-tenant database with Shared table Structures?

Answer 3

Separate banks give you more security. But it’s easier to implement an approach using the same bank. If you can, go from separate seats.