Scalability, architecture , Multitenancy : Database

Scalability, architecture , Multitenancy : Database

Assume that you have multiple clients, each client has access to similar kind of data, so database architecture should remain same for every client. 

An example for the same is , assume that you have developed an ERP product for a company, many companies will be having same requirement so the database will remain same.

Most of the developers will be under confusion, whether to go for single instance of database or multiple instance of database. Following are the two approaches and their pros and cons.

Approach 1 : Single instance database

  Keeping single instance of database but providing client id for each client to keep track of data of each client. 

Advantages : 

1) Better resource utilization – If your database is hosted on cloud services, like Amazon then as you create more number of instances they will allocate resources accordingly and charges will be applied for each instance. 
Example – check pricing for db instance in amazon

2) Scalability – Scalability is more easy since single instance is maintained.

Problems : 

1) Security issues – since every client’s data is in the same instance, developers should be wise enough to take care of showing data to appropriate users. Highest risk factor with single instance is security

2) Data loss – if the instance fails due to some problems related to one client, it affects to every other client, so routinely back of database is must be taken care. No compromise with data loss.

3) Performance issue – For a huge set of data performance of database might be low ( this matters for very huge data, not even for medium scale database )

This is called as Multitenancy – a principle in software architecture where a single instance of the software runs on a server, serving multiple tenants.

Approach 2 : Multiple instances database

with this approach all the problems of first approach will be solved but the only problem with this approach is scalability.