Oracle APEX 21.1/Oracle 19c 家庭贡献跟踪系统表设计咨询

一、基础表结构调整 #

首先优化你现有的两张核心表，移除冗余字段：

1. FAMILY_MEMBERS（家庭成员表） #

FAMILY_MEMBERS
---------------------------------------------
FAMILY_MEMBER_ID    NUMBER          PK
MEMBER_NAME         VARCHAR2(50)    NOT NULL
FAMILY_ROLE         VARCHAR2(20)    NOT NULL  -- 固定枚举值：'PARENT'/'CHILD'，禁止自由文本
CREATED_BY          VARCHAR2(50)    NOT NULL
DOR                 DATE            NOT NULL
UPDATED_BY          VARCHAR2(50)    NOT NULL
DLU                 DATE            NOT NULL

说明：移除原表冗余的CONTRIBUTOR_ID字段，直接用FAMILY_MEMBER_ID作为成员唯一标识，FAMILY_ROLE固定枚举值为后续按角色分配做基础。

2. FAMILY_CONTRIBUTIONS（家务项表） #

FAMILY_CONTRIBUTIONS
---------------------------------------------
CONTRIBUTION_ID     NUMBER          PK
PERIODICITY         VARCHAR2(50)    NOT NULL
CONTRIBUTION_DESC   VARCHAR2(1000)  NOT NULL
ASSIGNMENT_TYPE     VARCHAR2(20)    NOT NULL  -- 枚举值：'SINGLE'/'MULTIPLE'/'ALL'/'ROLE_BASED'
TARGET_ROLE         VARCHAR2(20)                -- 仅当ASSIGNMENT_TYPE为ROLE_BASED时赋值：'PARENT'/'CHILD'
MON                 CHAR(1)         NOT NULL
TUE                 CHAR(1)         NOT NULL
WED                 CHAR(1)         NOT NULL  -- 补充原表漏写的周三字段
THU                 CHAR(1)         NOT NULL
FRI                 CHAR(1)         NOT NULL
SAT                 CHAR(1)         NOT NULL
SUN                 CHAR(1)         NOT NULL
CREATED_BY          VARCHAR2(50)    NOT NULL
DOR                 DATE            NOT NULL
UPDATED_BY          VARCHAR2(50)    NOT NULL
DLU                 DATE            NOT NULL

说明：移除原表不确定用途的CONTRIBUTOR_ID字段，新增分配类型标记字段，适配四类分配场景。

二、关联表设计（解决多对象、条件分配需求） #

新增CONTRIBUTION_ASSIGNMENTS表作为家务和分配对象的关联中间表，覆盖所有分配场景：

CONTRIBUTION_ASSIGNMENTS
---------------------------------------------
ASSIGNMENT_ID       NUMBER          PK
CONTRIBUTION_ID     NUMBER          NOT NULL  -- 外键关联FAMILY_CONTRIBUTIONS.CONTRIBUTION_ID
ASSIGNEE_TYPE       VARCHAR2(20)    NOT NULL  -- 枚举值：'MEMBER'/'ROLE'/'ALL'
ASSIGNEE_VALUE      VARCHAR2(50)    NOT NULL  -- 对应值：成员ID/角色值/*（代表全体）
CREATED_BY          VARCHAR2(50)    NOT NULL
DOR                 DATE            NOT NULL
UPDATED_BY          VARCHAR2(50)    NOT NULL
DLU                 DATE            NOT NULL

-- 新增联合唯一约束，避免重复分配
CONSTRAINT UNQ_ASSIGNMENT UNIQUE (CONTRIBUTION_ID, ASSIGNEE_TYPE, ASSIGNEE_VALUE)

三、四类分配场景实现逻辑 #

• 单成员分配：ASSIGNMENT_TYPE设为SINGLE，关联表插入1条ASSIGNEE_TYPE='MEMBER'、ASSIGNEE_VALUE为对应成员ID的记录即可
• 多成员分配：ASSIGNMENT_TYPE设为MULTIPLE，关联表插入N条对应成员的MEMBER类型记录即可
• 全体成员分配：ASSIGNMENT_TYPE设为ALL，关联表插入1条ASSIGNEE_TYPE='ALL'、ASSIGNEE_VALUE='*'的记录即可
• 按角色条件分配：ASSIGNMENT_TYPE设为ROLE_BASED，TARGET_ROLE赋值为对应角色，关联表插入1条ASSIGNEE_TYPE='ROLE'、ASSIGNEE_VALUE为对应角色值的记录即可

四、周轮换逻辑适配 #

你提到的每周数值递增到4重置的轮换规则，建议新增一张WEEKLY_CONTRIBUTION_LOGS周执行记录表，通过APEX应用逻辑或者Oracle定时任务（DBMS_SCHEDULER）每周自动生成：

1. 定时任务每周固定时间执行，读取所有有效家务项和分配规则
2. 结合当前轮换值、分配规则，批量生成当周每个成员对应的家务执行记录
3. 后续统计、展示直接读取该日志表即可，无需每次动态计算分配规则，性能更高、查询更方便

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