Single or separate databases for separate customer accounts?
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.
- Not only does this fully secure data that MUST NEVER be shared
- 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.
- Backup, restore operations and strategies can easily be formulated for independent databases
- Constraints and uniqueness are easier and more naturally expressed
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.