none
Single Database with Multiple Schemas vs. Multiple Database

    General discussion

  • I have a web application that is a single portal for multiple applications. Currently the database server is setup in a multiple database fashion with a single database for each dataset. For example, the user information and portal layout is controlled by the PORTAL database and an application would be in the APPLICATION_1 database. I found this to be easier to manage the data for each application independently and installation new application means copying a database from development to production without need for scripting. I also found that I could backup and recover a single application vs all applications in one large file. The downsides are performance for queries between databases, the inability to create foreign keys between databases, and having to manage the server setup. I am contemplating the idea of creating a single database and using different schema names to organize the data. This will allow me to keep a similar naming convention, create the proper foreign keys, and increase performance. I am wondering if anyone could weigh in the upsides and downsides of this change?
    Monday, August 01, 2011 1:21 AM

All replies