Home Forums Database Design Design Ideas and Questions Is it Better to use Multiple DB than Single DB? RE: Is it Better to use Multiple DB than Single DB?

  • July 16, 2013 at 9:54 pm

    #1633065

    PaulB-TheOneAndOnly (7/16/2013)

    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.

    Your 2 cents are worth a million bucks. "Divide'n'Conquer" works well even at the database level.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)