Solution

Schema

The diagram can also be found online on dbdiagram.io.

Table user {
  id integer [primary key, increment]
  // we don't choose UPN as primary key, see STATIC_NATURALKEYS
  username char(255) [not null, unique]
  salt char(255) [not null]
  password char(255) [not null]
}

Table post {
  id integer [primary key, increment]
  // a title would be nice
  title char(255) [not null]
  // a slug will be automatically created based upon the blog post's title; this makes our blog more SEO friendly
  slug char(255) [not null]
  content text [not null]
  status post_status [not null, default:`DRAFT`]
  // a blog post can belong to a category
  category_id integer [null, ref: > category.id]
  // we could also use author_id or user_id as column name to specify the role of a user
  // @see USE_WELLKNOWN_COLUMNS
  created_by integer [not null, ref: > user.id]
  created_at datetime [not null]
}

// A post can only one have these status
Enum post_status {
  DRAFT
  PUBLISHED
  REVOKED
}

Table tag {
  id integer [primary key, increment]
  name char(255) [not null, unique]
}

// This is the m:n relation table between a post and its tags
Table post_in_tag {
  id integer [primary key]
  tag_id integer [not null, ref: > tag.id]
  post_id integer [not null, ref: > post.id]
}

Table category {
  id integer [primary key, increment]
  name char(255) [not null, unique]
}