Multi-tenant db versus single-tenant db

[ShadowCloud]

Hi Guys,

So I'm currently stuck between deciding on using a single database for a SAAS application or having a "System" database for the application and having each client's database generated on the fly?  So essentially single vs multi tenant instances.

I do like they idea of not having the maintenance headache of keeping all the schemas in sync by using a single database, however, I don't like the idea of having to expose everything to a clientID column (Either in the table or via the derived joins to get to a clientID)

Does anyone have some actual experience using this?  I'm finding very conflicting information both online and from the developers I work with...

[Lenoch]

Can't you use views? Although that might get you some performance problems.

[ShadowCloud]

I could potentially string along views through the application but I'd need to check what the performance limitations are.  This won't get rid of my concern with regards to ClientIDs in all my tables and/or joins to a client table.

My primary concern is what this decision will do to my codebase and day to day maintenance.  The backups and backup structure will change, if I go single tenant I need to write an export module to give client data back upon request not to mention the turnaround time for a rollback?

If I choose the multi tenant approach I'll have the opposite problem, I database backup will give all the data back to the client without worrying about anything else, connection context can be changed depending on the logged in user so I don't have the joins and ClientID concern everywhere in my codebase, however, then I have the nightmare of keeping schemas in sync across releases which could become rather tedious?

I'm actually starting to think I should consider a Hybrid approach to this?  Have a single instance for some of the system related stuff but keep the client data in separate databases?  Try to get the best of both worlds?

[TheWormKill]

I am not sure how well a hybrid approach will work out, otherwise it would be more common and/or better documented from other projects, blog posts, books and special purpose tools. I'll probably try to develop a flexible, maintainable schema first and use the second approach, since you could keep the maintenance to a minimum and extend in a sane manner. This has a downside however: it is hard to realize. You need to know the requirements in great detail and plan everything. But essentially, this is "the right way".

[ShadowCloud]

I am not sure how well a hybrid approach will work out, otherwise it would be more common and/or better documented from other projects, blog posts, books and special purpose tools. I'll probably try to develop a flexible, maintainable schema first and use the second approach, since you could keep the maintenance to a minimum and extend in a sane manner. This has a downside however: it is hard to realize. You need to know the requirements in great detail and plan everything. But essentially, this is "the right way".

mmm, I think that's just about the best advice I could follow based on the information I have provided for you to deal with.  Upon looking at my requirements and the amount of expansion I'm planning for this (if all goes well) I've decided on the single tenant approach.  I'd rather deal with the ClientIDs in my joins and extension methods can ensure I don't output anything the user doesn't have access to.  It's a little more work on the codebase but I'm more comfortable dealing with issues in the code than on the schema

[skruf]

It's almost impossible to answer your question, sadly, because alot of critical information is lacking. The multiple vs single tenant question relies on several factors. We have no idea of the architecture of your system, how many clients, etc.

Do you have any numbers ? #clients, #connections, data? To put it super simply, skimming both the fiscal cost and time investments I'd usually say: Single (it's quicker and easier), if you are within the constrains of a single database server (and staying there), and NEED the added value of advanced data mining structures you can consider multiple. I have basic experience with multiple tenant sass applications and I stumbled upon a shit paper, but it describes mult. tenants ok:

https://sdqweb.ipd.kit.edu/publications/pdfs/KrMoKo2012-closer-multitenant-sass.pdf

To reiterate: Gather more info, dont listen to us. Look at the demands of the application and the chances you guys have for investment. Multiple tenant solution has a higher chance of yielding better returns, but it requires substantial effort to accomplish.

I'd highly recommend against a hybrid solution unless it's a research project. For once the silly buzzwords are viable; think of scalability (important as FCK if you're growing fast), availability, and MAINTAINABILITY. If you're keeping the application alive for years to come, being able to rely on the work of other people becomes increasingly important and maintainability is a huge factor.