Why Activity Tables Need Status Fields: Beyond Start and End Times

The author begins by questioning whether a marketing activity table can rely solely on start_time and end_time without a status field. At first glance, three temporal states can be derived:

Not started: WHERE start_time > NOW() In progress: WHERE start_time <= NOW() AND end_time >= NOW() Ended: WHERE end_time < NOW() However, adding a status column introduces operational overhead: when an activity ends naturally, a scheduled job or delayed message queue must update the status, which is not real‑time and creates edge‑case boundary issues.

The article then pivots to the operational perspective. Real‑world scenarios require manual intervention:

An activity may need to be forcibly terminated before its scheduled end time due to a fault or business decision. Most activity management backends provide a "terminate" or "void" button for this.

Safety controls are needed to prevent losses from operator errors. Two common patterns exist: an approval workflow ( approving state) or a draft‑to‑release flow where an activity stays in draft until a human explicitly clicks "publish".

Consequently, the status field becomes essential. The author proposes a VARCHAR(20) column with a default of 'draft' and four values: draft – 草稿 (draft) approving – 审核中 (under review) release – 已上线 (released) cancelled – 已作废 (cancelled)

With both dimensions present, a valid "currently running" activity is queried by combining status and time:

SELECT *
FROM activity
WHERE status = 'release'
  AND start_time <= NOW()
  AND end_time >= NOW();

To fetch all unexpired valid activities (including those not yet started):

SELECT *
FROM activity
WHERE status = 'release'
  AND end_time >= NOW();

The conclusion: time fields alone are insufficient because they cannot express human‑driven state changes or approval gates. Best practice is to separate control state ( status) from temporal state ( start_time, end_time), using all three fields together to fully represent an activity's lifecycle.