database design, your thoughts on an issue with developers

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Development » database design, your thoughts on an issue...

database design, your thoughts on an issue with developers

Rate Topic

Display Mode

Topic Options

Author

Message

dbdmora

Posted Wednesday, January 09, 2013 9:45 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, January 09, 2013 2:00 PM
Points: 20, Visits: 64

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

Sean Lange

Posted Wednesday, January 09, 2013 10:11 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:45 PM
Points: 8,567, Visits: 8,218

dbdmora (1/9/2013)

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

Post #1404886

Lowell

Posted Wednesday, January 09, 2013 12:45 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 6:06 AM
Points: 11,614, Visits: 27,673

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

Evil Kraig F

Posted Wednesday, January 09, 2013 12:53 PM

SSCertifiable

Group: General Forum Members
Last Login: Saturday, May 18, 2013 10:09 PM
Points: 5,658, Visits: 6,100

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

David Webb-CDS

Posted Wednesday, January 09, 2013 12:57 PM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 3:22 PM
Points: 823, Visits: 5,678

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

dbdmora

Posted Wednesday, January 09, 2013 1:26 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, January 09, 2013 2:00 PM
Points: 20, Visits: 64

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 »

Permissions