SQL Clone
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
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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
SSC Eights!
SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)

Group: General Forum Members
Points: 966 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
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56267 Visits: 32798
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
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6646 Visits: 4262
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
SSC Eights!
SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)SSC Eights! (946 reputation)

Group: General Forum Members
Points: 946 Visits: 730
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 Guru
SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)SSC Guru (117K reputation)

Group: General Forum Members
Points: 117874 Visits: 41454
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
krayknot
krayknot
SSC Eights!
SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)SSC Eights! (966 reputation)

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