Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Should applications share a database? Expand / Collapse
Author
Message
Posted Wednesday, October 22, 2008 1:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 21, 2011 12:26 PM
Points: 4, Visits: 60
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
Post #590069
Posted Thursday, October 23, 2008 12:13 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 7, 2011 1:41 AM
Points: 346, Visits: 534
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
Post #590264
Posted Thursday, October 23, 2008 6:25 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 12:12 PM
Points: 15,517, Visits: 27,896
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #590405
Posted Thursday, October 23, 2008 8:52 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:43 AM
Points: 1,849, Visits: 2,009
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.
Post #590513
Posted Wednesday, November 5, 2008 7:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 10:54 AM
Points: 162, Visits: 318
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.
Post #597350
Posted Wednesday, November 5, 2008 9:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:14 AM
Points: 36,735, Visits: 31,186
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #597884
Posted Wednesday, November 5, 2008 10:24 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 7, 2011 1:41 AM
Points: 346, Visits: 534
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
Post #597901
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse