Single or separate databases for separate customer accounts?

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开发者_如何学C 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?

---

We have gone down both routes here. We have a shared database for smaller clients who do not need customization and a separate database (and application code base for that matter) for Enterprise clients who do.

Both have their pluses and minuses. In the shared database all it takes is one bad query where someone forgot to use the clientid and the data is exposed to other clients. In five years, I have seen this happen only once but it was a major nightmare that cost us a client. If you go this route, make sure you have a good QA team that checks for exactly that before releasing code to prod. If your database has schemas, you can use views in schemas to prevent data access of other client data. If the client only has the rights to their views, then they can;t ever see anyone else's data. This is more work to set up though.

But the separate databases can become a nightmare to make maintenance changes to. However, if you sell your upgrades, this is the way to go as not every client will buy each upgrade. Just make sure you have a good tracking mechanism to know what version each client is on and that you use source control to track all database changes by version, so you can easily upgrade.

If you don't intend to have customizations, you may find that having separate datbases leads in that direction anyway. It's a lot easier to tell them no when they areon a shared database.

Further we've found that there are customizations that would be helpful to other clients but because they are developed in a separate application and database, they get redeveloped by a different team for a different client and you end up with 6 ways to do the same thing. This then becomes a major pain for people who work across clients such as the people who import client data to the databases. Personally I prefer the one database approach.

Another point concerning the need to consolidate or separate out concerns reporting on the data. If reporting will always be done only by client, then you can separate them out, but if you need to do reporting (such as your own internal finacial reporting) woth consolidated data, it is much easier if you have a consolidated database. I bring this up becasue people tend to forget about reporting until after the design is set and it can cause some pretty nasty problems when you do that.

---

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

---

IMHO there is only one way. Multiple databases.

1. Not only does this fully secure data that MUST NEVER be shared
2. It also enables the schemas to change independently of each other. By that I mean that over time, maybe one tenant is much bigger than all the other teanants, and thus special needs must be catered for.
3. Backup, restore operations and strategies can easily be formulated for independent databases
4. Constraints and uniqueness are easier and more naturally expressed