Should applications share a database?

  • I have a general database design question.

    Our company has multiple hosted web applications. Each of those applications currently use a single sign solution that reads from a database with general client information in it. Our "Client" DB holds client information for each of our applications.

    Each of our applications also has it's own own database.

    My question is, is it better to manage all client information in a central client database as we are, and push client information to individual application databases? Or is it a better architecture to store individual client information in each application's DB and push that data to a central Client DB that can be read from for reporting, billing and such.

    Does this make sense? Can anyone offer resources that help with making these kind of database design questions?

    Thanks

  • sam (10/22/2008)


    I have a general database design question.

    Our company has multiple hosted web applications. Each of those applications currently use a single sign solution that reads from a database with general client information in it. Our "Client" DB holds client information for each of our applications.

    Each of our applications also has it's own own database.

    My question is, is it better to manage all client information in a central client database as we are, and push client information to individual application databases? Or is it a better architecture to store individual client information in each application's DB and push that data to a central Client DB that can be read from for reporting, billing and such.

    Does this make sense? Can anyone offer resources that help with making these kind of database design questions?

    Thanks

    you should go for the Client - Server db architecture

    kshitij kumar
    kshitij@krayknot.com
    www.krayknot.com

  • I'm not aware of a resource for making these types of decisions except for common sense and experience. You could go to a consulting firm or bounce the ideas off of a research firm like Gardner.

    You have to weigh costs, benefits and risks. For example, splitting up each client into a seperate database means added maintenance because you have to backup more databases, run consistency checks on more databases, update statistics, defrag indexes... you get the picture. Plus, you have to create mechanisms for maintaining common data available across each database and mechanisms for gathering information that you want from these databases. That is set against the ability you'll gain to isolate each client from the others, easily, for security. Also you'll be able to scale each client independently, even seperating them to other servers as needed. You can also provide database tuning individually for clients that use some part of the app more than others. Also, by splitting up the client databases, you reduce the risk to all clients if one database gets corrupted.

    See, you can go around and around with it. You just have to make the decisions within your own environment. Largely, I'd say, it really depends on size. If each of the database is 5gb, it's probably not worth all the labor to split them apart.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • sam (10/22/2008)


    My question is, is it better to manage all client information in a central client database as we are, and push client information to individual application databases? Or is it a better architecture to store individual client information in each application's DB and push that data to a central Client DB that can be read from for reporting, billing and such.

    How is the central client database information currently maintained? It may be more difficult to maintain this information with each individual application. If it is fairly simmilar across all of your applications, there could be redundant data that will have to me maintained separately for each application then. It depends on what client information you are storing and how frequently it changes I guess.

  • Both approaches are viable, and under different circumstances could be considered the best approach.

    If for example, you use a domain account for the login, the user login and pw are stored in a central location (domain controller) and on each user database, you store a tokin that maps to the central database.

    The opposite approach could also be the best, depending on your needs, and the business expectations.

    Both have pros and cons. Both are viable.

    The more you are prepared, the less you need it.

  • krayknot (10/23/2008)


    you should go for the Client - Server db architecture

    Are you saying one client per Server???

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

  • Jeff Moden (11/5/2008)


    krayknot (10/23/2008)


    you should go for the Client - Server db architecture

    Are you saying one client per Server???

    not one client per server. it should be in the form of Star topology.

    you can also use SQLite, if you are thinking to use different db's for client and then after to update the central server db with the client's dB.

    kshitij kumar
    kshitij@krayknot.com
    www.krayknot.com

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply