How do I use Zend_Db_Table with multiple schemata and multiple deployments?

My boss wants the application we are currently working on to be split across several schemata in the database, because he wants multiple applications -- some of which I have no control over -- to be able to access the data, following a naming convention like DeploymentPrefix_Category. For instance, there'd be a few schemata for production, Production_Foo, Production_Bar, and Production_Baz, and then the same for staging Staging_Foo, Staging_Bar, and Staging_Baz, and the same for development.

The problem is that while Zend_Db_Table lets me specify a schema, it doesn't seem to let me generate that schema on the fly, which I would need to do to put that prefix on the schema.

What's the best way to handle that?

The issue of different configs for different environments is easily handled with Zend_Config. See the section on config in the quickstart:

http://framework.zend.com/manual/en/learning.quickstart.create-project.html

This allows you to specify different settings for each environment.

As for the schemas, I'm guessing you have some tables that live in Production_Foo and others that live in Production_Bar. Consider extending Zend_Db_Table for each of these schemas and pointing to the correct database at the time of construction.

Zend_Db_Table's constructor is defined as follows:

public function __construct($config = array(), $definition = null)
    { ... }

When we follow through to see where $definition leads it allows you to pass an array that is loaded into Zend_Db_Table_Definition. One of the options for this is the table name:

/**
 * @param string $tableName
 * @param array  $tableConfig
 * @return Zend_Db_Table_Definition
 */
public function setTableConfig($tableName, array $tableConfig)
{
    // @todo logic here
    $tableConfig[Zend_Db_Table::DEFINITION_CONFIG_NAME] = $tableName;
    $tableConfig[Zend_Db_Table::DEFINITION] = $this;

    if (!isset($tableConfig[Zend_Db_Table::NAME])) {
        $tableConfig[Zend_Db_Table::NAME] = $tableName;
    }

    $this->_tableConfigs[$tableName] = $tableConfig;
    return $this;
}

As for your schema, you just pass in different set of options for the db adapter that points to the correct one.

Well i'd argue that it's not "good" to have different table-names for different staging scenarios "Production_Foo" - "Staging_Foo" - "Testing_Foo".... just "Foo" is so much easier and more productive...

But anyways: Personally i use the Table-Data-Gateway (i guess that's what it's called) - using Zend_Db_Table_Abstract extensions, so i would do it like this:

class Application_Model_DbTable_Foo extends Zend_Db_Table_Abstract 
{
  public function __construct($config = array()) {
    $this->_name = Zend_Registry::get('config')->env_tbl_prefix.'Foo';
    parent::__construct($config);
  }
}

Obviously this requires you to have the config stored to the registry and inside the config to define the key "env_tbl_prefix" with your environment prefixes "Production_", "Staging_", "Testing_", etc...

Still, you're the developer, tell your boss to make life easier for all of you ^^ There's so many disadvantages using different table names depending on environment :\