Topic: ONE DB PER TENANT

I am using MYSQL for my Ruby on rails web application and I want to use the option of one DB per tenant, can anyone share any sample or tutorial to achieve the same? I have seen few videos, etc, but none tell me how to let two clients simultaneously access the same web application while connecting to different DB's. I have tried some samples by providing establish connection in before filter and using magic multi connection gem (it seems obsolete and association does not work in it).
Hence this question. thanks in advance for any help, I am looking for help urgently on this topic.

Re: ONE DB PER TENANT

you won't find much as it's an extremely BAD design
It's far easier to have a model connect to a specific database but not a record and tennants are by their very nature a physical entity and therefore represented in a single table as a number of records

If you want to connect a model to a different database then I did a small tutorial on this but you will need to dig further and fully understand the implications of what you are doing http://railsforum.com/viewtopic.php?id=42143

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: ONE DB PER TENANT

Hi James, I am not sure if you understood my question and not sure why you call this design bad, because in one of the rails conference video, by Guy, I think, he has suggested this as one of the option (3rd option of identifying tenant based on log in id), why is it a bad design to have one DB (same tables in all DB but different instance of DB) per tenant.
Basically what I want is, if the URL is www.myapp.com, and user is user 1, I want to connect to DB1, however if the user is user 2, I want to connect to DB 2.
And user 1 and user 2 might be parallely using the app from different machines.
Please help.
Thanks in advance.

Re: ONE DB PER TENANT

Please help.

Sure smile

in one of the rails conference video, by Guy, I think, he has suggested this as one of the option (3rd option of identifying tenant based on log in id

I am not familiar with that video cast so I can't really comment on it but nothing in that quote indicates multiple db's per tennant

and not sure why you call this design bad

For a number of very many reasons.
1) To have a table store just one record is a bad database design. You should/could use a yml or some form of config file to handle a single record

2) Consider the process of creating a new tenant. Every new tenant that signs up you are going to have to programatically create a new database which can be a challenge but having said that a challenge is not really a good reason not to do it.

3) Performance and resources. this is the real biggy.
You loose the benefit of any connection pooling. You will need huge resources from your host. 100 databases is not really an issue but what happens if you have 50,000 tenants? will your host allow that many databases, How will your RDBMS handle it?
It just isn't scaleable and websites HAVE to have scaleability at the heart of their design. You have to be able to handle 1,000's of requests per minute. Even on a relatively small site with not much iser traffic you have to consider the number of bot's spiders, crawlers etc... that are going to be hitting the site. you are going to ned a LOT of RAM to handle instantiating a lot of databases.

4) Deployment. Make a database change in your migrations and you will have to update every single database!!! What happens if someone signs up half way through a migration and the new database is missed?

5) What other related tables are there? This could prove a nightmare of a coding issue for any users that are not tenants trying to get a list of tenants or related tenant data.

I don't think I have covered the really important issues surrounding this yet but I'm starting to wonder if I should throw the question back to you. Why would you want to take this approach?

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: ONE DB PER TENANT

Thanks James, here is the link to Guy's video:

http://aac2009.confreaks.com/06-feb-200 … -naor.html

Now coming to my requirement, we are building a healthcare informatics solution (web based), through which various doctors from various hospitals would log in to see the cases assigned to them and review the cases plus do some reporting.
Here the concept of a tenant is a chain of hospitals who are present in multiple cities, so junior doctors in remote cities can refer cases to senior doctors within their group. Each group corresponds to one tenant.
Since it is health care information and it contains patient sensitive data, our customers are very worried about data of one tenant becoming available to another if stored within one single DB. Hence they are kind of requesting us to maintain seperate DB for each tenant. Number of tenants in my application will not grow very fast, say in next 5 years, at max 20 - 25 customers.
Hope this helps you understand my need, let me know if you need any further information to help me out.
Thanks once again.

Re: ONE DB PER TENANT

BTW, James, I did not understand your below statement:
1) To have a table store just one record is a bad database design. You should/could use a yml or some form of config file to handle a single record

Why would my table only contain one record? Each DB will have multiple tables and each table will have multiple records.

Thanks,
Kartik

Re: ONE DB PER TENANT

Now I understand your requirement my point 1 is irrelevant

Taking the following 2 points

our customers are very worried about data of one tenant becoming available to another if stored within one single DB

and

Since it is health care information and it contains patient sensitive data, our customers are very worried about data of one tenant becoming available to another if stored within one single DB

A separate DB for each group would absolutely NOT guarantee any separation of data any more than keeping the data in one DB.
As far as the project and technical designs I have done for the NHS in the UK are concerned the only acceptable solution was to have total separation of apps with an api for patient transfer between apps.

To provide you with a detail/spec for your requirement is way beyond the scope of any forum advice that you should accept as you will have a number of legal requirements to take into account and you really need to get your project manager to design the solution for you properly and is what your clients are presumably paying for.

So I have to come back to the original question and answer it in isolation but I think I have already done that.

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: ONE DB PER TENANT

We are trying to provide software as service to different customers who could not afford separate installation at their site.
All i want to know is, knowing that it is a bad design, all I want to achieve is with a single code based running on the server, I want different customers to log in and view their data, however each customer has a different instance of DB ( although model/schema across all the DB s of all the customers are same). How can I achieve that with ROR? Since whatever options (doing explicit establish connection in models) that I tried (except for magic multi connections gem) do not work, means the DB connection gets overridden for the first customer who logged in when the second customer logs in, and the first customer gets connected to the second customer's DB. Magic multi connections gem works (partially), but it does not work for associations, like objectA.ObjectB, and it seems that gem is not being maintained by anyone now. I have heard of DB charmer and master slave option in it, however not sure I can use that in my case. However seeing Guy's video, I felt that it should be possible in some way using ROR.

Re: ONE DB PER TENANT

You have to use establish_connection if you want to switch db's and you could use the database.yml file to provide the appropriate environment for the establish connection method. This would probably work if you set up each group in your database yml file and have an abstract base model that all models descend from which has the sole job of determining which environment/group to connect to based on who is logged in.
The best way to achieve that would be to have a central db of all users and have each user assigned to a group and a central login that uses that master db. Once you have that the base model should be able to establish the connection to the correct database.
As all models should descend from that base model then they will automatically get the right connection established.
You will have a lot of work to do in the base model to ensure that the correct connection is established for EVERY database hit and you are heading for a big fall if you continue with this approach, but if you must then that's probably the way to do it.

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)