unnikozhissery (7/10/2013)
In need to design an Enterprise level solution which will contain around 8 modules (such as Accounting, Human Resource, Inventory, Purchases etc). It will be a portal with single sign on to different modules.Now the question is whether to put all the tables of all the modules in one DB or to have different DB for each module. All the modules will be interlinked (E.g. Users will be pulled from the Human Resources in the Purchase module. And any purchases made will be moved to inventory and transaction will be posted in Accounts too).
A rough estimate is that each module will have 100+ tables so in total 800+ tables in the whole system. In one module the transaction will be huge (2 million entries per month in a single table - will be archived at the end of the year)
So what will be best way to design the database. Both advantages and disadvantages
Putting everything together in a single database sound as a good idea but, I have seen more than enough good ideas turning bad.
Think on the big picture...
How about scalability?
What is going to happen in the future if this system grows and grows? how are you planning to scale it? May be a solution would be to scale it out, on different servers in which case having different databases would make your life much easier.
How about recoverability?
What is going to happen if your HR Vice President comes one day and says, I'm so sorry, please recover the database as it was last Friday at 5:32PM? Having separate databases would make this easier? provided you have a sound backup/recovery strategy in place.
How about business continuity?
What is going to happen when you are planning your Business Continuity/Disaster Recovery plan? some applications like "billing" would be critical, on the "the company cannot survive without them" category while other applications like "HR" would be less than critical, perhaps on the "company can survive for two weeks without it" category. Having separate databases will help too, don't you think so?
In short, even if it sound counter intuitive probably having separate databases would give you a more powerful, more versatile platform to support your organization.
Just my two cents.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.