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

database design, your thoughts on an issue with developers Expand / Collapse
Author
Message
Posted Wednesday, January 9, 2013 9:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:24 PM
Points: 20, Visits: 71
I have a developer that wants to create 4 different databases on a specific server that what technically seems for 1 app.

ITApplicationSecurity
ITApplicationState
ITApplicationService
ITApplicationLogging

The same application will write to each database based on what function is performing. I told him it would be a better idea to create one database and schema out each function that the application will perform.

Developer came back saying that this design is to support multiple applications and separating the databases out will help with System performance.

Just another fact, the DB server they want it on already has 60+ databases. I just think four databases for 1 app is over kill.

Your thoughts?

Post #1404868
Posted Wednesday, January 9, 2013 10:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:04 PM
Points: 13,086, Visits: 12,553
dbdmora (1/9/2013)
I have a developer that wants to create 4 different databases on a specific server that what technically seems for 1 app.

ITApplicationSecurity
ITApplicationState
ITApplicationService
ITApplicationLogging

The same application will write to each database based on what function is performing. I told him it would be a better idea to create one database and schema out each function that the application will perform.

Developer came back saying that this design is to support multiple applications and separating the databases out will help with System performance.

Just another fact, the DB server they want it on already has 60+ databases. I just think four databases for 1 app is over kill.

Your thoughts?



Without more details it is hard to have a strong feeling one way or the other. However just given the names of the databases they all sound like a single table db???

Maybe a database named ITApplication with 4 tables?
Security
State
Service
Logging

Of course I would not allow table names that are that vague but that is sort of how I am reading it.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1404886
Posted Wednesday, January 9, 2013 12:45 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:09 PM
Points: 12,905, Visits: 32,166
i would go with one app=one database.
it sounds like they have four suites of tables related to a given functionality.

with the current planned design, at some point, you can be sure that some report or functionality will need data combined into a single report.

That is much more easily fixed with your plan for a single unified database.

Otherwise, pulling report data into datatables, merging the data with Linq or something, or else you run into cross database issues, especially with potentially different users for each database.

Based on the name, for the "Logging" database, i bet it's a common log used for all three modules, anyway.


i'd make them use a single database, and just add a naming convention to group suites of tables together
itAppSec_Invoices
itAppSec_Status
itAppState_Stuff
itAppService_Stuff
itAppLogging_Stuff


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1404954
Posted Wednesday, January 9, 2013 12:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:08 PM
Points: 5,401, Visits: 7,514
If you'd like an easy discussion with your developers, here's my usual approach. Databases are really just backup containers. If the information in these four databases needs to be kept in sync on data restores, then they're one app to the database layer. If not, let 'em loose.

It's hard from a schema/structural standpoint to decide one way or the other as to how this should really be organized, we just don't know enough.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1404958
Posted Wednesday, January 9, 2013 12:57 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 1:15 PM
Points: 863, Visits: 7,352
You might also ask him exactly what performance gains he expects to see with multiple databases.



And then again, I might be wrong ...
David Webb
Post #1404961
Posted Wednesday, January 9, 2013 1:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:24 PM
Points: 20, Visits: 71
Thanks for the responses all.

The argument came down to, when should a DBA (operations) need to know about the four databases. Development teams say it should be at the QA\staging phase once they are done developing. I feel it should be before development to make sure we know where the database will go once it reaches production. Developers want to create as many DBS as they want in Dev and once it goes to qa/staging then we can tell them where it goes. Only issue I have with this is that we have dedicated DEV\QA servers that mimic production. Why develop on the wrong server if eventually we might move it. Then cause more work for DBAs and devs to move the databases.

We are meeting next week to go over the details on why they need 5 now seperate databases. It will be interesting meeting.

P.S just an fyi, my department does not have strong SQL developers mostly Operations DBAs. This is one of the setbacks i have currently.
Post #1404976
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse