[development] Separating configuration vs data tables

[Khalid B]

On 6/9/06, Moshe Weitzman <weitzman at tejasa.com> wrote:
> > 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

This is something I was thinking about the other day, but from a different
angle. Some CMS have built in support for "production", "test" and
"development" environments. I think RoR and Plone have this.

Then I thought that multi site is so easy in Drupal that one can use
the same codebase with 3 different databases (or 3 different prefixes in
the same db).

Now what Barry is proposing "configuration" (or "system") vs. "content" is
a really good  differentiator here, and could be used in many situations,
such as sharing tables between sites, upgrades, ...etc.