Creating the ABQ database

Now that we've modeled our data and gotten a feel for the data types available, it's time to build our database. Make sure you've installed PostgreSQL and created the abq database as described in the first section of this chapter, and let's begin writing SQL to create our database structure.

Under your project root folder, create a new directory called sql. Inside the sql folder, create a file called create_db.sql. We'll start writing our table definition queries in this file.

Creating our tables

The order in which we create our tables is significant. Any table referred to in a foreign key relationship will need to exist before the relationship is defined. Because of this, it's best to start with your lookup tables and follow the chain of one-to-many relationships until all the tables are created. In our ERD, that takes us from roughly the upper left to the lower right.

Creating the lookup tables

We need to...