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)