How Do I Set Up A Relational Database To Use With Rails?

First of all, because Rails defines at least three different runtime environments (development, test, and production), you should create a database for each.

If you're using MySQL, start by creating three databases. Name them cookbook_dev, cookbook_test, and cookbook_prod. To do this, log into MySQL as the root user:

$ mysql -u root

If you don't have root access to MySQL, have your system administrator create a MySQL user for you that can create databases and users. At the mysql prompt, enter:

mysql> create database cookbook_dev; 
mysql> create database cookbook_test; 
mysql> create database cookbook_prod;

Now, create a user named rails_user and grant that user access to all tables in each of the databases you just created. (The password used here is "r8!lz" but you should take care to pick your own secure password.)

mysql> grant all privileges on cookbook_dev.* to 'rails_user'@'localhost'
    ->   identified by 'r8!lz';
mysql> grant all privileges on cookbook_test.* to 'rails_user'@'localhost'
    ->   identified by 'r8!lz';
mysql> grant all privileges on cookbook_prod.* to 'rails_user'@'localhost'
    ->   identified by 'r8!lz';

Next, create a file called create-mysql-db.sql containing the following (note that the following table creation syntax requires MySQL 4.1 or greater):

drop table if exists 'chapters';
create table chapters (
    id                             int not null auto_increment,
    title                          varchar(255) not null,
    sort_order                     int not null default 0,
        primary key (id)
) type=innodb;

drop table if exists 'recipes';
create table recipes (
    id                             int not null auto_increment,
    chapter_id                     int not null,
    title                          varchar(255) not null,
    problem                        text not null,
    solution                       text not null,
    discussion                     text not null,
    see_also                       text null,
    sort_order                     int not null default 0,
        primary key (id, chapter_id, title), 
        foreign key (chapter_id) references chapters(id)
) type=innodb;

drop table if exists 'tags';
create table tags (
    id                             int not null auto_increment,
    name                           varchar(80) not null,
        primary key (id)
) type=innodb;

drop table if exists 'recipes_tags';
create table recipes_tags (
    recipe_id                      int not null,
    tag_id                         int not null,
        primary key (recipe_id, tag_id),
        foreign key (recipe_id) references recipes(id),
        foreign key (tag_id)  references tags(id)
) type=innodb;

Now build the cookbook_dev database using the table creation statements in create-mysql-db.sql:

$ mysql cookbook_dev -u rails_user -p < create-mysql-db.sql
$ mysql cookbook_test -u rails_user -p < create-mysql-db.sql
$ mysql cookbook_prod -u rails_user -p < create-mysql-db.sql

Finally, verify successful creation of cookbook_dev database with the following command. You should see all the tables created with create-mysql-db.sql:

$ mysql cookbook_dev -u rails_user -p <<< "show tables;" 
Enter password:
Tables_in_cookbook_dev
chapters
recipes
recipes_tags
tags

If you're a PostgreSQL user, here's how to perform the same tasks. Start by creating a user and then create each database with that user as its owner. Log into PostgreSQL using the psql utility. The user you log in as must have privileges to create databases and roles (or users).

$ psql -U rob -W template1

template1 is PostgreSQL's default template database and is used here just as an environment to create new databases. Again, have your system administrator set you up if you don't have these privileges. From the psql prompt, create a user:

template1=# create user rails_user encrypted password 'r8!lz';
CREATE ROLE

Then create each database, specifying the owner:

template1=# create database cookbook_dev owner rails_user;
CREATE DATABASE
template1=# create database cookbook_test owner rails_user;
CREATE DATABASE
template1=# create database cookbook_prod owner rails_user;
CREATE DATABASE

Next, create a file called create-postgresql-db.sql containing:

create table chapters (
    id                             serial unique primary key,
    title                          varchar(255) not null,
    sort_order                     int not null default 0
);

create table recipes (
    id                             serial unique primary key,
    chapter_id                     int not null,
    title                          varchar(255) not null,
    problem                        text not null,
    solution                       text not null,
    discussion                     text not null,
    see_also                       text null,
    sort_order                     int not null default 0,
        foreign key (chapter_id) references chapters(id)
);

create table tags (
    id                             serial unique primary key,
    name                           varchar(80) not null
);

create table recipes_tags (
    recipe_id                      serial unique
        references recipes(id),
    tag_id                         serial unique
        references tags(id)
);

Then build each database using create-postgresql-db.sql:

$ psql -U rails_user -W cookbook_dev < create-pgsql-db.sql
$ psql -U rails_user -W cookbook_test < create-pgsql-db.sql
$ psql -U rails_user -W cookbook_prod < create-pgsql-db.sql

Finally, verify success with:

$ psql -U rails_user -W cookbook_dev <<< "\dt"
Password for user rails_user: 
             List of relations
 Schema |     Name     | Type  |   Owner    
--------+--------------+-------+------------
 public | chapters     | table | rails_user
 public | recipes      | table | rails_user
 public | recipes_tags | table | rails_user
 public | tags         | table | rails_user
(4 rows)

This solution creates a cookbook database and then runs a Data Definition Language (DDL) script to create the tables. The DDL defines four tables named chapters, recipes, tags, and recipes_tags. The conventions used in the names of both the tables and fields are chosen to be compatible with Active Record's defaults. Specifically, the table names are plural, each table (with the exception of recipes_tags) has a primary key named id, and columns that reference other tables begin with the singular form of the referenced table name, followed by _id. Additionally, this database is said to be in third normal form (3NF)—which is something to shoot for unless you have good reasons not to.

The table's chapters and recipes have a one-to-many relationship: one chapter can have many recipes. This is an asymmetric relationship in that recipes do not belong to more than one chapter.

The solution also describes a many-to-many relationship between the recipes and tags tables. In this case, recipes can be associated with many tags, and symmetrically, tags may be associated with many recipes. The recipes_tags table keeps track of this relationship and is called an intermediate join table (or just a join table). recipes_tags is unique in that it has dual primary keys, each of which is also a foreign key. Active Record expects intermediate join tables to be named with a concatenation of the tables it joins, in alphabetical order.

It's a good question, but the answer's changed to be a lot simpler since that recipe was originally written. You don't need to write DDL statements any longer - Rails can do that for you.

The default database for Rails in development mode is now SQLite, so you can postpone the MySQL setup until later, though if you build anything large and put it into production, you will, of course, want to. (With SQLite, you can just go ahead and build a Rails application without pausing for database setup, though SQLite doesn't scale very well.)

Robble's directions for creating the MySQL user and database(s) still look good to me. You should put the user, password, and database name into the config/database.yml file, something like:

development:
  adapter: mysql
  database: cookbook_dev
  username: cooky
  password: ch0c0lat3
  timeout: 5000

You don't need to set up the tables and their relations in MySQL itself - the migrations you create while building your Rails application will take care of that for you. (Dan Chak, in Enterprise Rails, strenuously disagrees with that position, but for my non-enterprise-scale work, it's a lot easier to let Rails deal with the database details.)