Suppose you've written a custom Drupal module and you now need to add a new table to the module.

The standard Drupal method is to (a) add the new table to hook_schema and then (b) write an appropriate hook_update to create the new table. 

To minimize code duplication and the chance of error the best way I've found to do this is to reuse the definition from the hook_schema in the aforementioned hook_update.

Worked Example

Like all things code based this is best illustrated using an example, suppose your custom module (called "my_payroll_module") needs a new table called "myemployee_table" the   hook schema excerpt might then be:

  function my_payroll_module_schema() {
    $schema['myemployee_table'] = array(
      'description' => 'Employee name table',
      'fields' => array(
         'employee_id' => array(
         'type' => 'varchar',
         'length' => 20,
         'not null' => true),
      'name' => array(
         'type' => 'varchar',
         'length' => 100,
         'not null' => true),
      'department' => array(
         'type' => 'varchar',
         'length' => 30,
         'not null' => true),
      'primary key' => array('employee_id'),
    return $schema;

The simplest form of the hook_update would then be as follows:

  function my_payroll_mopule_update_6001() {
    $ret = array();
    $schema = my_payroll_module_schema();
    db_create_table($ret, 'myemployee_table',$schema['myemployee_table']);
    return $ret; 

So as you can see we use the definition already defined (in the schema array) to build the table using db_create_table. Easy!

Updated for D7

The above worked example was written for Drupal 6, now the only real difference with Drupal 7 is the function db_create_table uses a different parameter signature, so the function would now be:

  function my_payroll_mopule_update_7001() {
    $schema = my_payroll_module_schema();

The benefit of using the schema is that you get to test your schema definition as well!

The Drawback

Like all things there is a drawback to this method, and it is not the recommended approach (see, the main reason being that this approach essentially makes the particular update un-repeatable if there are subsequent updates to the named table schema.

The reason for this is that the hook update takes the latest schema definition, whereas in fact there may be numerous subsequent updates to that named table... Essentially the recommended approach us to repeat the schema definition in the hook update!