One Big Database vs. Several Smaller Ones
We have a situation were we can (A) deploy instances of an applications in one MySQL database using table prefixing or (B) use different MySQL databases for each instance of the application, for e.g.,
central_database app1_table1 app1_table2 app1_tablen ... appn_table1 appn_table2 appn_tablen
The end result being a large db with many tables.
app1_db table1 table2 tablen ... appn_db table1 table2 tablen
The end result being many databases with some tables.
All things equal (e.g., amount of data, number of app instances, etc), what are the pros and cons of going with either approach? What would be detrimental to database performance and maintenance? The application is PHP 5 based, run over Apache 2.x, and we're running MySQL 5.x.
Many thanks for your time and thoughts!