Java状态模式下多属性状态的Broadcast类PostgreSQL表设计及状态流转留存咨询

设计思路 #

采用「广播主表+状态变更流水表」的分离架构：

• 主表仅存储Broadcast类中与状态无关的公共静态属性，避免冗余存储
• 状态变更流水表独立存储每一次状态生效的全量数据，每条记录对应一次状态实例，天然支持同一状态多次生效的场景，也能完整保留所有状态转换的历史轨迹

表结构实现 #

首先自定义PostgreSQL枚举类型统一管理状态值：

CREATE TYPE broadcast_state AS ENUM ('UPCOMING', 'LIVE', 'ENDED');

1. 广播主表 broadcast #

存储广播的核心基础信息：

CREATE TABLE broadcast (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    title VARCHAR(255) NOT NULL,
    author_id BIGINT NOT NULL, -- 关联作者表主键，根据实际Author表结构调整
    current_state_event_id BIGINT, -- 关联最新状态事件ID，方便快速查询当前状态
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

2. 状态变更流水表 broadcast_state_events #

存储每一次状态生效的完整数据，是实现全量状态轨迹保留、同状态多次生效的核心表：

CREATE TABLE broadcast_state_events (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    broadcast_id BIGINT NOT NULL REFERENCES broadcast(id) ON DELETE CASCADE,
    state_type broadcast_state NOT NULL,
    version INT NOT NULL, -- 同广播下状态版本号，每次状态变更自增，保证顺序唯一
    -- 各状态独有字段，非当前状态对应字段可留空
    upcoming_created_at TIMESTAMPTZ, -- UPCOMING状态独有：创建时间
    scheduled_at TIMESTAMPTZ, -- UPCOMING状态独有：预定开播时间
    live_started_at TIMESTAMPTZ, -- LIVE状态独有：开播时间
    ended_started_at TIMESTAMPTZ, -- ENDED状态独有：实际开播时间
    ended_at TIMESTAMPTZ, -- ENDED状态独有：结束时间
    effective_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 本次状态生效时间
    UNIQUE(broadcast_id, version) -- 同广播下版本号唯一
);
-- 加索引方便查询单广播的所有状态历史
CREATE INDEX idx_broadcast_state_events_bid ON broadcast_state_events(broadcast_id);

如果状态后续可能扩展更多独有字段，也可以用JSONB类型统一存储扩展属性，简化表结构：

-- 替代上面的状态独有字段
state_extra JSONB NOT NULL DEFAULT '{}'::JSONB

使用示例 #

针对你提到的流转路径 upcoming → live（第一次）→ live（第二次）→ ended，插入对应数据：

-- 1. 先插入广播主记录
INSERT INTO broadcast(title, author_id) VALUES ('春季新品发布会', 1001) RETURNING id; -- 假设返回id=1

-- 2. 插入UPCOMING状态记录，version=1
INSERT INTO broadcast_state_events(broadcast_id, state_type, version, upcoming_created_at, scheduled_at)
VALUES (1, 'UPCOMING', 1, '2024-05-01 10:00:00+08', '2024-05-10 19:00:00+08');

-- 3. 插入第一次LIVE状态记录，version=2
INSERT INTO broadcast_state_events(broadcast_id, state_type, version, live_started_at)
VALUES (1, 'LIVE', 2, '2024-05-10 19:01:23+08');

-- 4. 插入第二次LIVE状态记录（比如断流后重开），version=3
INSERT INTO broadcast_state_events(broadcast_id, state_type, version, live_started_at)
VALUES (1, 'LIVE', 3, '2024-05-10 19:15:46+08');

-- 5. 插入ENDED状态记录，version=4
INSERT INTO broadcast_state_events(broadcast_id, state_type, version, ended_started_at, ended_at)
VALUES (1, 'ENDED', 4, '2024-05-10 19:01:23+08', '2024-05-10 21:03:12+08');

-- 6. 更新主表的当前状态ID
UPDATE broadcast SET current_state_event_id = (SELECT id FROM broadcast_state_events WHERE broadcast_id=1 AND version=4) WHERE id=1;

查询完整状态流转路径：

SELECT state_type, version, effective_at FROM broadcast_state_events WHERE broadcast_id=1 ORDER BY version ASC;

即可得到完整的转换轨迹，两次LIVE状态会作为两条独立记录返回，数据不会被覆盖。

内容的提问来源于stack exchange，提问作者unityflim