需求梳理 #

用户创建用药提醒时，需指定药物、用药时长（起止日期）、每日服药次数，系统会按固定间隔（24小时/每日次数）生成所有提醒时间戳——比如每日3次、时长1周的话，要生成21条提醒记录。现有主表MedicationReminder，需要设计合理结构存储这些多频次的时间戳。

推荐设计方案 #

方案1：主表+明细表（预生成所有时间戳） #

这和你初步的思路匹配，拆分两个表避免数据冗余，同时能精细跟踪每个提醒的状态：

1.1 主表 medication_reminders

存储提醒的核心配置信息：

CREATE TABLE medication_reminders (
    reminder_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id) NOT NULL,
    medication_id INT REFERENCES medications(medication_id) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    total_pills INT NOT NULL,
    daily_dosage_count INT NOT NULL, -- 每日服药次数
    interval_hours NUMERIC NOT NULL, -- 自动计算：24 / daily_dosage_count，比如3次就是8
    is_active BOOLEAN DEFAULT TRUE
);

• interval_hours可在应用层创建提醒时计算，或用数据库触发器自动生成，避免重复计算
• 关联用户表和药物表（假设你已具备这两张基础表）

1.2 明细表 reminder_timestamps

存储每个具体的提醒时间戳及对应状态：

CREATE TABLE reminder_timestamps (
    timestamp_id SERIAL PRIMARY KEY,
    reminder_id INT REFERENCES medication_reminders(reminder_id) ON DELETE CASCADE NOT NULL,
    scheduled_at TIMESTAMP WITH TIME ZONE NOT NULL,
    is_sent BOOLEAN DEFAULT FALSE, -- 标记是否已发送通知
    sent_at TIMESTAMP WITH TIME ZONE, -- 实际发送时间
    user_confirmed BOOLEAN, -- 用户是否确认服药（可选）
    confirmed_at TIMESTAMP WITH TIME ZONE
);

• 用户创建提醒时，应用层或触发器自动生成从start_date到end_date的所有scheduled_at记录
• ON DELETE CASCADE保证删除主提醒时，对应的时间戳也会被清理，避免脏数据

方案2：存储规则，动态计算时间戳（适合灵活场景） #

如果不想预生成大量记录，也可以只存储规则，查询时用PostgreSQL函数动态生成时间戳：

修改主表，增加首次服药时间字段：

CREATE TABLE medication_reminders (
    reminder_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id) NOT NULL,
    medication_id INT REFERENCES medications(medication_id) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    total_pills INT NOT NULL,
    daily_dosage_count INT NOT NULL,
    first_dose_time TIME NOT NULL, -- 比如首次服药时间设为08:00
    is_active BOOLEAN DEFAULT TRUE
);

• 查询时用generate_series生成所有时间戳：

SELECT 
    reminder_id,
    generate_series(
        (start_date + first_dose_time)::TIMESTAMP WITH TIME ZONE,
        (end_date + first_dose_time)::TIMESTAMP WITH TIME ZONE,
        (INTERVAL '24 hours' / daily_dosage_count)
    ) AS scheduled_at
FROM medication_reminders
WHERE reminder_id = 123;

• 优点：节省存储空间，修改规则更便捷
• 缺点：每次查询都要计算，无法跟踪单个提醒的发送状态或用户响应

方案选择建议 #

• 若需要跟踪每个提醒的发送状态、用户服药确认情况，选方案1更合适，每条时间戳都有独立状态字段
• 若提醒规则频繁变动，且不需要精细跟踪单个提醒状态，方案2更高效

优化小技巧 #

• 给reminder_timestamps的reminder_id和scheduled_at建立联合索引，提升查询效率：

CREATE INDEX idx_reminder_timestamps_scheduled ON reminder_timestamps(reminder_id, scheduled_at);

• 用TIMESTAMP WITH TIME ZONE处理时区问题，避免跨时区的时间错误
• 可通过数据库触发器自动生成reminder_timestamps记录，减少应用层逻辑负担

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