[development] Separating configuration vs data tables

[Moshe Weitzman]

> My suggestion is to identify which database tables contain 
> administrative or configuration data vs. which tables contain content 
> and then store them in separate databases (brief research indicates that 
> MySQL supports cross-database queries but Postgres does not, so this 
> functionality would be turned off with Postgres).  Ideally, it would 
> then be possible to clone both databases, add modules or change settings 
> in the sandbox until happy, and then copy back only the admin/config 
> database and any filesystem mods.  In one step, the production system 
> would be upgraded.
> 
> We would have to decide which tables fall into each category and 
> possibly refactor some tables if they span both categories.  Perhaps it 
> is not possible to separate them all cleanly in this way, but it seems 
> worth trying.
> 

right and right. it would be outstanding if we could pull this off. 
Migrating changes to production is a major headache, and this would make it 
really easy to roll forward and even roll back.

i used to work at a company which worked this way. we called relatively 
static tables 'domain' tables and relatively dynamic 'transactional'. i like 
your terms of configuration and content better i think.

yes, that prefix trick is very cool and requires no changes from core or 
contrib. but i have a better idea. Lets store this mapping in a new 
configuration table. Then, modules or the installer can write into this 
table. we just need to run this pseudocode in settings.php where the 
$db_prefix stuff is now:

// set prefixes for each table as specified by the prefixes table
$sql = SELECT prefix, name FROM prefixes;
$result = db_query($sql);
while ($row = db_fetch_object($result) {
   $db_prefix[$row->name] = $row->prefix;
}

from there on, queries will always hit the right DB.

as a next step, i sigest identifying which tables go where and resolving 
splitting up ambigous tables