0

As per Multi-Tenant Data Architecture post, there are 3 ways to implement multi-tenancy

  1. Separate Databases

  2. Shared Database, Separate Schemas

  3. Shared Database, Shared Schema

I have following details:

  1. User should be able to backup and restore their data.

  2. No of tenants : 3 (approx)

  3. Each tenant might belong to different domain(url).

  4. There are some common tables for all the tenants.

  5. No of tables in each tenant: 10 (initial)

I would like to know which approach is more suitable for me?

User5590
  • 1,383
  • 6
  • 26
  • 61
  • As per your requirements i think #2 will be good as you don't have much tenants – Ravi Oct 06 '15 at 05:13
  • As mentioned in point #4, there are some common tables for all the databases. How will i achieve that? – User5590 Oct 06 '15 at 05:20
  • While Fetching OR INSERT/UPPDATE YOU will know what data is required from which table(s) so you use appropriate table (common table or tenant wise table) – Ravi Oct 06 '15 at 06:42
  • Check this http://stackoverflow.com/questions/15683213/databse-architecture-single-db-vs-client-specific-db-for-building-enterprise-w/15697362#15697362 – techuser soma Oct 09 '15 at 14:38

4 Answers4

0

I think option 2 is the best one, but you still have an issue with requirement 1. Backup and restore is not available per schema. you will need to handle this using Import data or any custom tool. common tables will have separate schema.

In option 1, you need to handle requirement 4, common tables will be replicated between all databases.

shanyour
  • 304
  • 1
  • 9
  • Thanks for your reply. If i go with the option 1, how will i manage to update all the child database? – User5590 Oct 06 '15 at 05:58
0

The most important condition among all the 5 conditions is condition 4 - which says that some tables are common among all the tenant. If some tables are common then Separate Database (i.e. Option 1) is ruled out.

You could have gone ahead with option 2, shared database and separate schema but the number of tenants are quite less (3 in your case). In such a scenario adding the overhead of maintaining separate schemas is an overhead which should be avoided. Hence, we can skip Option 2 as well until we evaluate option 3.

Option 3 - Shared database with shared schema is will be most efficient option for you. It avoids the overhead of maintaining separate schemas and allows common tables among tenants. In shared schemas generally a tenant identifier is used across tables. Hibernate already has support for such tenant identifiers (just in case you will use Java-J2EE for implementation). The only problem may be performance as putting the data of all three tenants together in the same table(s) will lead to lower database access\search performance which you will have to counter with de-normalization and indexing.

I would recommend going forward with option 3.

Dhruv Rai Puri
  • 1,335
  • 11
  • 20
0

For us, we have developed an ERP in HR which is used by about a forty of client and a thousand's of users; The approach used for the multi-tenancy is the third.

In addition, one of the technical separation tables used was the heritance.

bilelovitch
  • 1,975
  • 1
  • 16
  • 24
  • I also think that i will go for option 3. But what should i do for common tables? – User5590 Oct 07 '15 at 05:08
  • For the option 3, the common tables will be shared between all the tenants (clients). – bilelovitch Oct 08 '15 at 13:11
  • 1
    I'm interested how this works. Do you have a `clientId` column in every single table to separate the data? I imagine an ERP has tons of data, so how performant is the system? Also, how do you handle backups for a single client when all the data is mixed? – Ivan Jun 15 '22 at 02:51
0

I had the same issue in my system. We had an ad-network system that became pretty large overtime, so I was considering migration to multi-tenant architecture per publisher.

Option 3 wasn't relevant as some publishers had special requirements (additional columns/procedures and we are not supporting partitioning). Option 2 wasn't relevant since we had concurrency and load issues between customers. In order to reach high performance, with plans to scale-out up to 1000 publishers, with 20 widgets and high concurrency, Our only solution was Option 1 - Separate Databases.

We already migrated to this mode, and we have 10 databases running, with a shared database for configuration and ads. From performance, it is great. From high availability, it is also very good as each publisher traffic doesn't effect all the other. Adding new publisher is an easy step for us, as we have our template environment. The only issue we have is Maintenance.

Lately I've been reading about PartitionDB, It looks very simple to manage as you can have a gate database to perform all maintenance works including upgrades and top level queries. It supports common shared database (same we have already), and I am trying now to understand how to use the stand alone database as well.

Johns
  • 31
  • 2