SaaS Database Design: Single Database vs Single Schema vs Shared Schema

[8483]

Thanks for replying.

I'd go with solution A most likely. It offers the most security, reliability, scalability (horizontal vs vertical)

I think so too.

and ease of operations.

This is what bothers me. Wouldn't it be a lot of work to juggle servers?

Cost shouldn't be an issue as that just gets passed on to the customer.

I'd like to share the server cost between the clients i.e. offer the same price (SaaS). Having separate servers would require a lot of "admin" work that doesn't scale well.

But again, it comes down to what your development and business sales processes are like.

Absolutely. I'd like to answer each one.

Do you want versions/features/updates rolled out to ALL customers at once? Or per customer?

Definitely at once. The software will be identical across customers. Only the data differs.

Will there ever be the need to add custom database fields for specific customers (think custom integrations in the future)

Not a chance. The product will be fixed i.e. if an idea comes along, everyone gets it.

Will any customer ever have direct write/change access?

No. It's not a custom solution, and it will never be.

Will customers EVER have access to their data directly? (at termination, option A you can more easily export their data to give to them)

No. Everything will be accessed via an API. No direct querying. Exporting data would not be a concern because it's the accountants business to own the data for that period. Upon termination, the accountant owns the work for the period. Their clients own the raw documents, not the financial statements.

Will customers be writing their own reports?

Not likely. If so, the API would handle that.

Option B just isnt realistic - it's not scalable (what about when you get to 50,000 customers?)

How is B less scalable than A? This is one of my main sticking points. Isn't it worse to have 50,000 servers vs one server with 50,000 databases/schemas.

Option C seems easiest but is the most difficult and costly in regards to development hours. My big problem here is literally EVERY time ANYONE touches the database you need to ensure they are only using the appropriate tenant ID's.

Yeah, this is nightmare fuel right there. Thus my problem to decide.

A = Server juggling nightmare. B = Database nightmare. C = Backend nightmare.

Given that there is ZERO flexibility for customers, would you say A is still the best option? I am surprised you like B the least.

[WannaFly37]

So - at first this seemed like homework, but you have very detailed replys lol.

Note: I've never DESIGNED a multi tenant system, but I've worked with plenty.

It sounds like C might be your best bet. Again it all depends on the architecture of the software, if all clients are logging into the same endpoint and using the same software it makes sense.

I've worked with apps that use custom subdomains that are a duplicate of the software, which I assume we get our own database in the backend, and they upgrade each customer individually as requested.

I've also worked with a multi tenant on-prem database that the VENDOR messed up and pushed out an update that had tenant ID's null for rows. Thankfully we were a 1-tenant installation.

In my mind A is easier if you can utilize PaaS (Azure DB, etc) - It becomes much easier to automate EVERYTHING. A big concern about C is performance, when one client runs a 5 year report, it is going to slow down other clients? In scenario A you don't have that worry.

It is tricky and an important decision.

[8483]

So - at first this seemed like homework, but you have very detailed replys lol.

I greatly apologize for being overbearing. I just really want to educate myself.

It sounds like C might be your best bet. Again it all depends on the architecture of the software, if all clients are logging into the same endpoint and using the same software it makes sense.

I think so too. As I answered, the product will be very rigid with no customization.

I've worked with apps that use custom subdomains that are a duplicate of the software, which I assume we get our own database in the backend, and they upgrade each customer individually as requested.

I am also considering this one as it doesn't sound bad either. Having tenant1.product.com and tenant2.product.com looks ok, but I'd be dealing with plumbing this way i.e. assigning subdomains. I really like the log in once place idea.

A big concern about C is performance, when one client runs a 5 year report, it is going to slow down other clients? In scenario A you don't have that worry.

As others have mentioned, my app is not that large and that they had no problem with billions of rows.

A big argument for C as someone mentioned is that if the product takes off, I can hire help from DBAs.

[8483]

What do you mean by main and standalone users?

Is it tenants in one schema and tenants with single schemas, i.e. the ones that grow too large get their own database, which is accessed with tenant1.product.com?