The not very useful answer would be: It depends on what you are trying to achieve.
More usefully (I hope): I work with a complex web application which is also strongly modularized. This system is generally delivered using several databases, residing on different servers, to allow us to cope with more user activity. The whole set-up relies on quite complex replication, since some of the data in the system is required by all of the constituent parts. A large cluster, scaled to cope with thousands of users might theoretically consist of four database servers: Web, Master, Reporting, Processing. The first two are strongly paired, with the former serving all direct requests from web page users (e.g. viewing and submitting data). The second feeds 'centrally controlled' information (e.g. product information) to all of the other databases, and is the entry point for maintenance personnel who maintain the information within the system. The use of a reporting server is fairly standard - it allows us to shift the processing requirements of report generation onto a separate server, lowering the load on the web-facing one. Requests for reports are passed to this server which generates the required data and passes it to a separate system responsible for displaying/serving the finished report to the user. Finally the use of a 'processing' box, would theoretically allow any number crunching to be done away from the web-facing box - data requiring processing (e.g. an order which the user has submitted) is churned here, and the results are replicated back to other databases which require them. I don't know if it is technically possible to run cross-database transactions, but I would suspect not. However, I'm sure you can work around this without too much trouble - the use of replication so that all data 'originates' from one place and is controlled there can help to simplify this. To summarise, before I get carried away - if your application/system can be split down into logical sections, it may be advantageous both from the organizational point of view (as you say) and scalability pov too, to split it up into multiple databases. Thanks, Matt -----Original Message----- From: news [mailto:[EMAIL PROTECTED] On Behalf Of TO Sent: 21 March 2004 15:14 To: [EMAIL PROTECTED] Subject: multiple databases: design question What are the advantages and disadvantages of using multiple databases, versus placing all tables in one uber-database? I understand and appreciate the organizational value of multiple databases, but what other issues are involved? I ask this because I'm considering moving from tables across multiple DBs (on one server) to all tables in one DB (on one server.) I don't want to do this, but may need to given the constraints of perl's Class::DBI and Ima::DBI. Specifically, I am stuck on successfully handling transactions and rollback across multiple databases, because Ima::DBI creates a different handle (eg connection) to each database, which stymies rollback (as the work is happening thru different cxns). I'd welcome any suggestions about structuring databases -- one vs. many -- and if anyone has advice about the perl issues, that'd be great too. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
