What HopPress Must Store

We used a relational database to store CourseTracker’s data. HopPress can use a relational database too. If you read the functional requirements, you can see what HopPress must store. The requirements fit a relational data model with entities like users and posts, and relationships that capture authoring and editing. Relational databases also make “list published posts for a given author” a simple query.

Another reason a relational database fits HopPress well is that each post moves through fixed states (draft, submitted, published, returned) and only certain roles can trigger certain transitions. In the schema, that becomes columns like state, author_id, and published_at, plus constraints that enforce valid values and ownership.

Here is a schema for the relational database that HopPress could use:

users
  user_id            integer   primary key
  idp_subject        text      unique, not null
  name               text      not null
  email              text      unique, not null
  created_at         timestamp not null

user_roles
  user_id            integer   primary key, foreign key -> users
  role               text      primary key, check in ('author', 'editor', 'administrator')

posts
  post_id            integer   primary key
  author_id          integer   foreign key -> users, not null
  editor_id          integer   foreign key -> users, null
  title              text      not null
  body               text      not null
  state              text      not null, check in ('draft', 'submitted', 'published', 'returned')
  created_at         timestamp not null
  updated_at         timestamp not null
  published_at       timestamp null

A few notes on the schema:

  • HopPress does not store passwords. Sign-in is handled by the university’s identity provider, which returns a stable identifier for each account. That is idp_subject. It is unique, but it is issued by another system, so user_id is the primary key. That follows the rule from Chapter 2.
  • Readers are not users. A reader does not sign in and has no row.
  • A user can hold several roles, so roles are stored in the user_roles junction table. The set of roles is fixed by the software, not entered by administrators, so a check constraint is enough. There is no roles table.
  • state holds the post’s current state. published_at is null until the post is published.
  • editor_id is null until an editor is assigned to a post. author_id and editor_id are both foreign keys into users, because authors and editors are both users.
erDiagram
    direction LR
    users ||--o{ user_roles : holds
    users ||--o{ posts : authors
    users |o--o{ posts : edits

The rules about who may move a post between states are not in the schema. We will look at them next.