There is a web app (AngularJS frontend with ASP.Net Web API) used by multiple customers on a single instance. Each customer can have any number of users. The data of one customer is not visible to others. For this scenario, what is the best approach?
1) Single DB. Have Customer Id on all tables and control data visibility.
2) Multiple DBs. During login process, select the DB.
Is there any other approach to this?
This SO answer led me to this article
I have tried to shorten and summarize that article for my needs.
Single DB (Shared DB for all customer)
Pros: 1) has lower maintenance, equipment and backup costs 2) relatively difficult to develop
Cons: 1) depending on the application, some customer may not even consider a application with shared db. 2) restoring a customer's data from backups in the event of a failure is a relatively highly difficult. 3) incur additional development effort in the area of security, to ensure that customers can never access other customers' data, even in the event of unexpected bugs or attacks
Multiple DBs (Separate DB for each customer)
Pros: 1) makes it easy to extend the application's data model (discussed later) to meet customers' individual needs 2) restoring a customer's data from backups in the event of a failure is a relatively simple procedure
Cons: 1) lead to higher costs for maintaining equipment and backing up customer data 2) number of customers that can be housed on a given database server is limited by the number of databases that the server can support.
Choosing an Approach
Economic Considerations: Has initial high cost of development, however, ongoing operational costs tend to be lower.
Security Considerations: Prospective customers will have high expectations about security, and your service level agreements (SLAs) will need to provide required provisions
Customer Considerations: The number, nature, and needs of the customers you expect to serve - all needs to be considered.
Regulatory Considerations: Companies are often subject to regulatory law that can affect their security and record storage needs
Skill Set Considerations: Designing single-instance, multi-tenant architecture is still a very new skill, so subject matter expertise can be hard to come by.
