Which database schema should I use?

Question

I want to create a custom voting module to learn how to write a module. The vote table will contain four fields (id, title, vote_up, vote_down).
Should I create a new table? If I create a new database table, how do I keep the relationship with the node being voted?

Answer 1

If you want to create a voting module you should look into Voting API, it's an API module that offers common functions and database storage for voting modules to use.

However, since this is a learning excercise, you might want to do it all yourself, in this case:

• Use hook_schema() to define your database schema (the example on the documentation page is pretty self-explanatory!)
• In Drupal 6 you need to manually specify which schema to install so implement hook_install() and hook_uninstall():

function mymodule_install() {
  drupal_install_schema('mymodule');
}

function mymodule_uninstall() {
  drupal_uninstall_schema('mymodule');
}

An example of a hook_schema might look like this:

/**
 * Implementation of hook_schema()
 */
  function mymodule_schema() {
    $schema['mymodule_votes'] = array(
      'description' => 'Your schema',
      'fields' => array(
        'vid' => array(
          'description' => 'vote id',
          'type'        => 'serial',
          'unsigned'    => TRUE,
          'not null'    => TRUE,
        ),
        'nid' => array(
          'description' => 'node id',
          'type'        => 'int',
          'unsigned'    => TRUE,
          'not null'    => TRUE,
        ),
        'vote_up' => array(
          'description' => 'number of votes up',
          'type'        => 'int',
          'unsigned'    => TRUE,
          'not null'    => TRUE,
          'default'     => 0
        ),
        'vote_down' => array(
          'description' => 'number of votes down',
          'type'        => 'int',
          'unsigned'    => TRUE,
          'not null'    => TRUE,
          'default'     => 0
        ),
      ),
      'primary key'     => array('vid'),
      'indexes'         => array('node id' => array('nid'))
    );
    return $schema;
  }

Answer 2

Examples has a NodeAPI example that implements a five-star vote on nodes.

For a D7 version, you'd want to deal with entities instead, but it's a working example of how you might set up a schema for voting, based on your needs.