How would you implement a simple editorial status workflow?
Database state machine design.
Use ENUM or lookup table for status; enforce transitions in app code or a matrix table; audit actor and timestamp.
What's really being asked
This question evaluates whether you think in terms of state machines and data integrity rather than just adding a string column. The interviewer wants to see that you distinguish between storing a value and enforcing business rules, and that you know where to place constraints (database versus application layer). They also care about auditability and concurrency.
The full answer
First, the data type. Prefer a database-native ENUM or a small lookup table referenced by a foreign key over a plain VARCHAR. ENUM gives you storage efficiency and implicit constraint, while a lookup table is more portable and lets you attach metadata like labels or permissions. Second, transition enforcement. Never rely only on frontend code. The safest approach is an application-layer state machine that checks allowed transitions before writing. A more database-centric approach is a transitions table with allowed_from and allowed_to rows, optionally enforced by a trigger or by app code reading that matrix. Third, auditability. Add created_at and changed_by columns on the content table, or better, a separate status_history table recording every transition with old_value, new_value, actor_id, and changed_at. Fourth, concurrency. Mention optimistic locking with a version or updated_at timestamp so two editors cannot simultaneously move the same piece to different states.
The mistakes people make
Storing status as a VARCHAR with no CHECK constraint or foreign key. Relying on frontend dropdowns to enforce transitions. Proposing a single boolean is_published flag for a multi-step workflow. Suggesting you update the status in place with no history table and no actor tracking. Saying you would use a document store because relational databases cannot handle workflows.
What usually comes next
How would you handle permissions so only editors can move content to In Review and only admins can Publish? How would you query for all content that has been in review longer than three days? What happens if two users update the status at the same time? Would you ever push transition logic into the database with triggers, and what are the tradeoffs?
A concrete example
A content_pieces table has status_id as a foreign key to a content_statuses table containing Draft, In Review, and Published. A content_transitions table defines allowed moves, such as Draft to In Review and In Review to Published, but not Draft to Published. When a user submits an update, the application checks that the requested new status_id exists in content_transitions for the current status_id before committing. A content_status_history table records every change with actor and timestamp. This pattern mirrors how Drupal's Content Moderation module expands on basic published and unpublished states by introducing custom workflows and transitions between them.
Interview question
Which design best ensures valid editorial status transitions, full auditability, and safe concurrency control?
- a.Store status as an unconstrained VARCHAR, rely on frontend dropdowns to prevent invalid moves, and update the value in place without history.
- b.Use a single boolean is_published flag instead of multiple statuses, and depend on database row locking for concurrency.
- c.Store status as a database ENUM and enforce all transition logic through stored database triggers, relying on the row's updated_at timestamp for auditing.
- d.Reference a status lookup table, validate moves against a transitions matrix in application code, and log every change to a history table with optimistic locking.Correct
Why? this is the answer
The correct approach combines a lookup table for referential integrity, an application-layer transition matrix, a dedicated history table capturing actor and timestamp, and optimistic locking to prevent concurrent overwrites. Option C is tempting because ENUM is a valid storage choice, but relying solely on triggers and an updated_at timestamp fails to record who initiated each transition and omits the full audit trail required for a workflow.
Just read this? Test yourself on what you have been reading.
Read the original → drupal.org
- #database design
- #state machines
- #data integrity
- #workflow
- #content moderation
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Every open role lists the topics its interview covers, so you can prepare for the real thing rather than guessing.
See open roles