Hi,
Recently I have been working with a distributed database architecture in Drupal 6, without the use of multisite. So thought of sharing in the blog. Multiple database is very important in Drupal when you have custom tables along with Drupal inbuilt tables. The best practice is to keep separate database for both. For creating multiple database in Drupal, first you need to follow the conventional Drupal installation process. One's that is done, create a seperate database and dump all the custom tables in that database.
Assume the name of the drupal database is "drupal_db" and the name of the custom database is "business_db". Now after you have successfully completed installing Drupal in your server, go to sites/default/settings.php file. There you will find the following or similiar looking lines:
$db_url = 'mysqli://root@localhost/drupal_db';
$db_prefix = 'drp_';
1st step to convert the current Drupal setup into multi database, is to replace the above two lines with the following:
$db_url['default'] = 'mysqli://root@localhost/drupal_db';
$db_prefix['default'] = 'drp_';
$db_url['business_data'] = 'mysqli://root@localhost/business_db';
Basically you just need to create an array of connections. In this, $db_url['default'] refers to the default connection if you are not explicitly mentioning from which database the data needs to be fetched. Most cases this is the drupal database. For the custom database you need to create another index of the same array with the details of that database. In this case I have named it as "business_data" in:
$db_url['business_data'] = 'mysqli://root@localhost/business_db';
Now the main part is to switch connections as needed. For that I had used db_set_active() function. Generally I have used this switching process just before and after performing any database transaction. This function takes 1 parameter, which is the array index name which I had created earlier i.e. "default" and "business_data".
e.g.