A simple blog application has to be developed.
A blog can have multiple blog posts.
One blog post belongs to exactly one author. A blog post can have one of the following status: draft, published or revoked.
For better structuring, a blog post can have any number of tags. In addition to that, categories can be also used. One blog post can optionally belong to a category.
Additional questions
- How can the author be authenticated?
- Which additional attributes for a blog post might be of interest?
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]
}