Single or separate databases for separate customer accounts?

Question

I'm working on an app that collects data from smartcards. I want to be able to run the app as a web service for multiple customer accounts. The question is, should I create a separate database for each account or should I design a single database that holds all accounts' data? First, I thought that a single database was the obvious answer, but it results in the AccountID having to be used just about everywhere, in tables, indexes, constraints, queries, checks etc.

In this app, there is not a single byte of data that is to be shared between accounts.

First, let's look at how a separate database for one account would look:

CREATE TABLE CardHolder ( 
    CardHolderID int, -- primary key
    CardHolderUniqueName nvarchar(30) );

CREATE TABLE SmartCard (
    SmartCardID int, -- primary key
    CardHolderID int,
    CardUniqueName nvarchar(30) );

Add to that a few uniqueness constraints,

ALTER TABLE CardHolder ADD CONSTRAINT UQ_CardHolderName UNIQUE (CardHolderUniqueName);
ALTER TABLE SmartCard  ADD CONSTRAINT UQ_CardName UNIQUE (CardUniqueName);

Now, if I put everything in one database, it means that several accounts could handle the same CardHolders and SmartCards, but the accounts should not see each others data. Because of this, a SmartCard is unique within an account, but not within the entire database. So, every constraint must include an AccountID,

CREATE TABLE CardHolder ( 
    CardHolderID int, -- primary key
    CardHolderUniqueName nvarchar(30),
    AccountID int );

CREATE TABLE SmartCard (
    SmartCardID int, -- primary key
    CardHolderID int,
    CardUniqueName nvarchar(30)
    AccountID int );

ALTER TABLE CardHolder 
    ADD CONSTRAINT UQ_CardHolderName UNIQUE (AccountID, CardHolderUniqueName);
ALTER TABLE SmartCard
   ADD CONSTRAINT UQ_CardName UNIQUE (AccountID, CardUniqueName);

In the actual DB, there will be loads of more tables, columns and several indexes (for listing by expirydate etc etc) and the AccountID column has to be included everywhere.

It seems a bit cluttered to me, first putting all accounts in a single database, and then separating them by having an AccountID column in every table and just about every constraint and index. I'd also need to find or invent some sort of row level security to keep users from accessing other accounts' data. So, do I have a valid excuse for creating a separate database for each account, or do "real db designers" always keep everything in a single database?

Answer 1

There are several things to have in mind when designing a multi-tenant application, including, as your question states, the schema design, but things such as license costs, scalability etc. should also be taken into account. This article describes the three most common approaches for designing a multi-tenant application, including pros and cons. Check it out.

Link to a pdf version of the article: http://ramblingsofraju.com/wp-content/uploads/2016/08/Multi-Tenant-Data-Architecture.pdf