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 only takes a minute to sign up.
Sign up to join this community
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?
(FYI, I am designing my data store in MySQL, but general conceptual viewpoints are greatly appreciated)
Thanks.
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:
You cannot declare referential integrity constraints across databases.
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.
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.
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.
