Post States

The state column holds one of four values. The functional requirements say which values a post can move between and who can move it.

stateDiagram-v2
    [*] --> draft : author creates
    draft --> submitted : author submits
    returned --> submitted : author submits
    submitted --> published : editor publishes
    submitted --> returned : editor returns

There are no other transitions. In the initial release, published is final. A published post cannot be edited, unpublished, or deleted. Chapter 1 recorded a conflict between fast corrections and editorial control and left it open. The initial release chooses editorial control. Corrections will come in a later release.

Who may do what depends on the state and on the role of the user making the request:

Three rules are not visible in the diagram or the table:

• “Owning author” means the user whose user_id is the post’s author_id. Another author has no access to the post at all, in any state other than published.
• editor_id records the editor assigned to a post. It is null until an editor takes a submitted post for review. An editor lists the posts assigned to them by that column.
• An editor may not publish or return a post they authored. A user who holds both roles submits their own posts like any author, and a different editor decides. Without this rule, that user could publish their own posts, and the approval requirement would not apply to them.

You can add a check constraint to the schema so that author_id and editor_id are never the same: check (editor_id is null or editor_id <> author_id). Now the database refuses a post whose editor is its author.

The database cannot enforce every rule. The application must enforce some of them, before it writes to the database. That is fine. Deciding which rules the database enforces and which the application enforces is part of your job as the designer.