Recipe 3.1. Setting Up a Relational Database to Use with Rails

Problem

You installed MySQL or PostgreSQL installed, and you want to create a relational database for storing data about book chapters, recipes in those chapters, and tags that help with finding related topics across recipes. This database will be the backend for your Rails web application. The database includes one-to-many and many-to-many relationships: each chapter includes many recipes, but each recipe can be in only one chapter; each recipe can have several tags, and each tag can belong to many recipes.

Solution

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. For more on picking good passwords or passphrases, see http://world.std.com/~reinhold/diceware.html.)

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)

Discussion

The 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. Thinking about this data relationship should be intuitive and familiar: after all, this book is a concrete representation of it.

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.

Recipe 3.1. Setting Up a Relational Database to Use with Rails

Problem

Solution

Discussion

See Also