database design, your thoughts on an issue with developers

  • 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?

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • 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

  • 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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply