Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Should applications share a database?


Should applications share a database?

Author
Message
sam-667552
sam-667552
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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
krayknot
krayknot
Old Hand
Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)

Group: General Forum Members
Points: 348 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
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17625 Visits: 32268
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Chris Harshman
Chris Harshman
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2691 Visits: 3289
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.
Andrew..Peterson
Andrew..Peterson
Old Hand
Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)

Group: General Forum Members
Points: 342 Visits: 683
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.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45250 Visits: 39932
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
krayknot
krayknot
Old Hand
Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)

Group: General Forum Members
Points: 348 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search