Database Advice

Coffee Lounge
#1

In need of some advice for my next project.

It’s a web app used by firms to service their clients, record payments and generate recurring bills. I plan to then publish an API to allow their web designers to integrate a customer-facing management panel to their existing website.

In your opinion, which do you think I should go with…

  • Single database per tenant - Possibly more secure but increased overheads maintaining multiple databases and making updates very tedious. On the plus side, it offers the best solution for individual tenant migrations and backups/restores.

  • Single database with a schema per tenant - Only one database to manage, host and backup. Have never managed a database in this way so perhaps my limited knowledge is missing some crucial drawbacks?

  • Shared schema for all tenants - Would use a PK something like tenant_id to separate data. My fear is that backups & migrations would be complex for individual tenants and possible security/data leaks if I do not put enough security in place.

Maybe there’s a better solution?

#2

I think this really depends on your long term vision and goals. As you’ve mentioned, you could easily control data access based on some attribute that is related to each Account/User. It would require that each record has some type of reference to the Account to ensure only users within the account, or their customer’s are able to view the data. I think https://and.co works like this.

If you plan to allow the client to modify the system, integrate with other systems, or do more advanced setups, then my preference would be to go with a single tenant architecture – possibly spinning up a whole instance (web, app, db) for them. It will require more initial setup to support code pushes across all clients, but I think it would make it easier to support custom domains, not have to worry about data leakage/commingling, you can allow for DB encryption if a single client requires it, it’s easier to restore the database for a single client, and you could roll out new features to specific clients before pushing to everyone.

That being said, this is sort of like choosing between vanilla, chocolate, or strawberry. They’re all wonderful flavors, but some will like one more than the others. Each has advantages and disadvantages. I think choosing what is the best for you at the current time, while trying to look a few years ahead, is all you can do.

1 Like
#3

Find first if there is value in all 3 propositions.

What I mean is that : more maintenance = more headaches.

How much do you feel comfortable charging to do /outsource the job and not feel frustrated ?

Does your customer would clearly enjoy the benefit of higher security for that increased pricing ?

In term of 80/20 thinking…
Do you want to address a mass market to start with (aiming at the general 80%).
Or focus on premium offers for a niche within that market.

Once you have a better picture of the market fit, it becomes obvious that some technical choices won’t work. Because there’s no demand or a market not willing to put the investment for it. And the numbers won’t add up in term of income.

If you have no idea yet, implement the fastest solution and test quickly your hypothesis.

It’s easier to migrate 10 customers than 1000’s. :slight_smile:

2 Likes
#4

Thanks guys, some good comments. I’d rather not have to outsource until I really need too. My only concern with the single tenant architecture is once I scale to say even 10 or 20 clients, updating tables and making small customisations would start to be a pain.

#5

Here you have a great article by Microsoft that provides a very detailed overview of multi tenancy patterns.

I have resorted to this article in the past several times to take decisions.

1 Like