Schema

Back when I first argued for a relational database, I said you have to settle the structure before you store anything: which tables exist, which columns each table has, and how the tables link to each other. That settled structure is called a database schema.

A schema is the description of every table: its columns, the type each column holds, which columns are required, which are unique, which column or pair of columns is the primary key, and which columns are foreign keys into other tables. Every decision from the last several sections is part of it.

Here is the schema built so far:

courses
  course_number      text      primary key
  title              text
  credits            integer
  subject_area_id    integer   foreign key -> subject_areas

subject_areas
  subject_area_id    integer   primary key
  name               text

professors
  professor_id       integer   primary key
  name               text
  email              text      unique

offerings
  offering_id        integer   primary key
  course_number      text      foreign key -> courses
  professor_id       integer   foreign key -> professors
  term               text
  meeting_time       text
  room               text

students
  student_id         integer   primary key
  name               text
  email              text

requirements
  requirement_id     integer   primary key
  name               text

course_requirements
  course_number      text      primary key, foreign key -> courses
  requirement_id     integer   primary key, foreign key -> requirements

history_entries
  student_id         integer   primary key, foreign key -> students
  offering_id        integer   primary key, foreign key -> offerings

This schema is the blueprint for the database. It is the result of our design work so far. There is more work to do when designing the database, but this is a good point to leave it for this chapter. We will revisit other design concerns in later chapters that will affect the performance and usability of the database, among other things.