Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

What are the pros and cons to creating one huge database with numerous tables or better separating the data into many databases that each have fewer, more relevant tables?

  • Are there server performance issues associated with either design?
  • Are there memory issues with either design?
  • Are there connection issues with either design?

(FYI, I am designing my data store in MySQL, but general conceptual viewpoints are greatly appreciated)

Thanks.

share|improve this question
    
Does it make sense to separate these tables to different databases? –  FrustratedWithFormsDesigner Oct 29 '13 at 20:25
    
Yes, it might if there are no downside issues (like some of the ones I listed) @FrustratedWithFormsDesigner –  H. Ferrence Oct 29 '13 at 20:32

3 Answers 3

In general, a good RDBMS will handle both solutions the same way and you should not see performance differences. However, you do need to test with your data load on your (R)DBMS. MySQL in particular does not always follow the relational rule/mind set and your results might therefor vary.

There are two main differences that I can think of:

  1. You cannot declare referential integrity constraints across databases.

  2. If you split the data in several databases, restore from a backup might require a smaller downtime if you can restore the most important database first and bring up the application partially functioning. (This requires the App to be written in a way that it can gracefully handle partial database availability.) You could for example keep one month worth of data in a small active database and move older data to a larger archive database that also could live on cheaper (read: slower) storage. However, depending on your RDBMS, you might be able to achive the same without spreading across multiple databases. With SQL Server for example you can implement a similar solution within a single database by separating tables in different file groups. SQL Server allows a restore to happen one file group at a time. Once a file group is restored you can access its data. Accessing a table living on a not-yet-restored file group will fail with an error comparable to the one you get when accessing a normal database that has not yet finished restoring.

share|improve this answer
    
Thank you @SebastianMeine –  H. Ferrence Oct 30 '13 at 0:12

you'll want to separate them logically for the administrative hassle it would cause otherwise.

there are performance implications as well. you don't want multiple db's sharing the same buffer pool because access patterns vary and that really limits your control.

share|improve this answer

I can only comment on the SQL Server concept of databases, so this may or may not apply to other RDBMSs.

In SQL Server, a database is (1) the boundary for HADR (high availability and disaster recovery) and (2) the boundary for security.

Performance? SQL Server shares one buffer cache and one log cache for all sessions for all databases so there is little performance implication of one database vs many databases. If you have 15 megabytes of changes, that means 15 megabytes of traffic to be written to disk, regardless of how many databases it belongs to. Of course, more files does add a very small overhead in management traffic.

Connections? One client application makes one connection to the SQL Server database engine service and can then query multiple databases. The client does not need multiple connections for multiple databases. However, since a database is the security boundary then if you want a session to query multiple databases then the login will require permissions set separately in those databases.

In summary, usually one database is created for one application. For example, the Payroll database for the Payroll application.

share|improve this answer
    
Ok, thank you @GreenstoneWalker. One of the things I am wrestling with is that I plan to build an app now, and then extend it to add other apps that are not directly related to the first or each ensuing app. However, there will be one or more common databases (with related tables) that will be shared among each of these apps I plan to roll out. Example, I plan to implement a common customer/user database that all the apps will use. Also, I plan to have a settings/config DB as well. Outside of that each of the apps will have their own unique tables. So I was thinking is might be best to have –  H. Ferrence Oct 30 '13 at 12:49
    
many databases rather than 1 massive database. –  H. Ferrence Oct 30 '13 at 12:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.