Comparing 1 Database with Many Tables to Multiple Databases with Fewer Tables in Each 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.
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.