Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2010
    Posts
    2

    Big Database vs Multiple small databases

    Hi Guys!

    Im beginning the development of a new project and it will be a WEB application in ASP.NET, so I probably will use MS SQL.

    The information stored on the database will be access from many different locations BUT it need to keep it separated for each location. In other words, each location only can access their records. Each location potentially will generate millions of records and will be thousands of locations.

    This is my question:

    In your opinion, what is better/practical/easy to maintain?

    One Huge database, with security (o simple queries) to filter the data for each location

    Or

    One database for each location

    My concern is performance, but also how easy to maintaining will be.

    For example: Im afraid of making modifications to a table then need to replicate it to thousands of databases, in that case one database is more practical.

    At this moment Im not worry (yet) about infrastructure, servers, replication and that kind of stuff.

    Thanks in Advance!

    Edgar

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    One database and one set of tables is definitely easier to maintain. It should also be much easier to implement the security you want if you have all the data in one place.

    The main reason to have more than one database would be if you have very different availability or backup requirements or if you want to split the data over multiple servers. Otherwise put everything in one place.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    "Put all your eggs in one basket. And then, watch that basket!" -Mark Twain
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    hey what's up with this? you guys are agreeing on something???? )

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Don't worry - I know what to do:
    Use natural keys and make location the lead column of your clustered primary keys.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jul 2010
    Posts
    2
    Thank Guys! this was very helpfull!

Posting Permissions

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