CODEWITHSUNDEEP
postgresqldatabase-design
xxx_coder_noscope
xxx_coder_noscope

Reputation: 85

Blog post revisions with moderation step database design

I'm trying to design a database for the blog posts, that can be modified and diffs in revisions can be shown. In general, the flow looks like this:

  1. Create a template post. (update it at any time when in the draft state).
  2. When you are done with the template, you can pass it to moderation.
  3. Moderator can approve or deny your post (with the reason provided).
  4. If moderation is passed, then the post is published and can be viewed by the user.
  5. If the post was rejected you can apply a new version of it for moderation.
  6. When the post already published you can modify it through new revision (with moderation phase also).

My current DB schema for the article looks like this:

Article db schema

And it's not satisfying my requirements. I don't know how to model the moderation process in a database, with the possible rejection reason. So with all that in mind, what is the best approach?

Upvotes: 0

Views: 315

Answers (1)

Mike Organek
Mike Organek

Reputation: 12484

Per your question in the comments, I would structure the review history like so:

create table article_review_status (
  article_review_status_id int generated always as identity,
  status_code text not null unique
);

create table article_review (
  article_review_id int generated always as identity,
  article_revision_id int not null 
    references article_revision(article_revision_id),
  article_review_status_id int not null
    references article_review_status(article_review_status_id),
  entry_at timestamptz not null default now(),
  article_review_comments text,
  reviewer_id int not null
    references users(user_id) -- <-- Would this be another author or an admin?
);

Then your application can determine whether to make the article visible.

Upvotes: 2

Related Questions