Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2013
    Posts
    2

    Unanswered: Pros and Cons: 3 DBs or 1 DB

    Hello,

    I am developing an application that uses a database.

    As I have designing the database, I am thinking about the future framework for the system and wanted to engineer the database with the best practices.

    I have 3 main functions.
    - Employee Management
    - Equipment Management
    - Configuration Control

    I would like to know the pros and cons of creating 3 separate MSSQL databases on the same server and linked them with views versus 1 MSSQL database with different schema(s) for each function. Or is there a third recommended method.

    Thanks
    jlimited

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    If you are linking across the databases, then it is likely you have keys in various tables whose values will appear in two or three of the databases. This will make you pull your hair out. Foreign key constraints can not be enforced across databases. You will also want to take into account how to restore a database. if you restore one database, to a point in time in the past (say 24 hours ago), what will happen to any data in the other two databases that may depend or refer to the data that was entered in that 24 hour span, and is now lost?

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    One database, please.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by blindman View Post
    One database, please.
    I'll go with that suggestion. Previous company had several instances with multiple databases where developers joined tables across databases. Stupid idea. I saw this once before with another company and it's just flat files and nothing referenced correctly.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Mark Twain
    Put all your eggs in one basket. And then WATCH THAT BASKET!
    ...or was it Sam Clemens who said that?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •