Hi,

I am a data architect building a new database design for a web application that potentially will support hundreds of thousands of users.

Recently, one of our new hires suggested we design the database using a “multi-client” architecture.

The idea would be to build one database per user/client.

I was horrified when I first heard of this, though when I Googled it, it seems like many companies do it.

Here’s an article that explains the pros and cons that I found:
https://www.brentozar.com/archive/2011/06/how-design-multiclient-databases/#comment-3540812

It seems though that this sort of architecture might work Ok for a company building an application with several dozen clients with different data requirements, but not for a web application supporting hundreds of thousands of users.

Another idea that came up was having a schema per user rather than a database per user.

The advantage being that we could rollout changes for one group of users without affecting another group.

My feelings are that it should be the application code that controls whether a particular group of users has access to one set of features or not and not the database.

There is a security aspect in all of this as well, we have sensitive data and that is a concern we must address.

Have you ever heard or seen a “multi-client” database in action anywhere?

In your opinion, is this ever a “good” design pattern?