I'm building a personal Oracle APEX app to get more practice that would track our 'Family Contributions' (name we use for chores). Using the following chart / spreadsheet, what is the best way to design these data points in the database? I'm stumped on how to best handle the CONTRIBUTORS data.

A family contribution can be assigned to:

1. 1 family member
2. 2 family members
3. All family members
4. Conditionally based on contribution (some are just for kids, some just for parents)

Family contributions rotate weekly. Each week a number is assigned to a family member (incrementing number by 1 and 4 becomes 1) and it maps to the contribution id in the chart. My first crack (ignoring the CONTRIBUTOR column for now:

FAMILY_CONTRIBUTIONS
---------------------------------------------
CONTRIBUTION_ID     NUMBER          PK
PERIODICITY         VARCHAR2(50)    NOT NULL
CONTRIBUTOR_ID      NUMBER          NOT NULL  -- ???
CONTRIBUTION_DESC   VARCHAR2(1000)  NOT NULL
MON                 CHAR(1)         NOT NULL
TUE                 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

FAMILY_MEMBERS
---------------------------------------------
FAMILY_MEMBER_ID    NUMBER          PK
MEMBER_NAME         VARCHAR2(50)    NOT NULL
CONTRIBUTOR_ID      NUMBER          NOT NULL
FAMILY_ROLE         VARCHAR2(50)    NOT NULL
CREATED_BY          VARCHAR2(50)    NOT NULL
DOR                 DATE            NOT NULL
UPDATED_BY          VARCHAR2(50)    NOT NULL
DLU                 DATE            NOT NULL

I can handle cases 1, 2, 3 by using a child ASSIGNMENT table using rows of FAMILY_CONTRIBUTIONS.CONTRIBUTION_ID and FAMILY_MEMBERS.CONTRIBUTOR_ID, but even with that not sure how best to handle case 4.

CodePudding user response：

From purely database stand point i would recommend you create another table that has the contributers. So your family contributions table has the assigment, then the new table has the members assigned to a contribution. Column wise: primary id, contribution id and contributer id. When you select the contributions, you select every member assigned to a contribution from the new table, and left join with members table based on member id. Hope it makes sense.