• unnikozhissery (7/17/2013)


    And as of now we have decided to use different DB for each module

    There is a possibly big "gotcha" when using multiple databases for such a project that us folks recommending multiple databases forgot to mention. Declared Referential Integrity (DRI). You might know it better as "Foreign Keys". Foreign keys don't work across databases. If you need DRI across databases, you need some form of well written trigger. You can get around this somewhat if the DRI is to a small lookup table simply by having a copy of such "standard" lookup tables in each module which would allow each module (DB) to operate in a stand-alone fashion but, if you have module specific tables and there needs to be DRI between such table across modules then, like I said, you'll need triggers for the cross database DRI. If that's going to become a huge headache, it may very well be that a single database with a different schema for each module might be the better solution.

    Apologies for not thinking about that particular "little" nuance.

    --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)