Database strategy for over 10,000 instances of an ERP

Question

Hi , I have a moderately large rails ERP application having around 80 tables . I use MySQL . I recently received a client requirement which needs me to deploy the same application for roughly 10,000 offices(an instance for each office). The central office(superadmin office) must be able to view details from all these offices .

After talking with the client this is what i could come up with : Configure the app for 10,000 offices , there will be some new models for the data that need to be accessed by the superadmin . The tables for these models will be in another database (superdamin_database,not the one used by a particular office) , to which data to be reported to super admin will be written

I intend to make another app that would connect to this superdamin_database to present the data to super admin . So i will have 10000 app_databases and 1 superdamin_database

Where are my flaws in this plan ? What is the weakest point in this plan/structure that would render the application unusable due to a server load ? What is the rails way to do this . Hope the experienced guys in here would give a thought into this . Thanks in advance for your reply , Sam

Answer 1

Hi Sam,

if i understand your question correctly

I recently received a client requirement which needs me to deploy the same application for roughly 10,000 offices(an instance for each office).

you are violating the basic rule of a web application. Host in one place and access from anywere. I'm not sure why you want to install each of your application instances in these offices instead have a one hosted system which can be access from any office.

having hosted your application in one place will make your life easy when it comes to maintenance and upgrades.

BTW how on earth you are going to install 10,000 applications ?!

My advice is

Have a one single web application and let it used by your client (in the mentioned offices) and have a good layered access control system so that you can control which data access by which user

by that way you dont want to do this

I intend to make another app that would connect to this superdamin_database to present the data to super admin . So i will have 10000 app_databases and 1 superdamin_database

hope you got my idea,

cheers

sameera

Answer 2

Perhaps the concepts discussed at http://aac2009.confreaks.com/06-feb-2009-14-30-writing-multi-tenant-applications-in-rails-guy-naor.html will steer you in the right direction