You're structuring your data using nested structures. Instead, you should look for relations (tables), with a flat structure... and ask the database to join them when you need content from more than one. Don't be afraid to JOIN them all, this is what RDBMS are for.
You could use the following structure. I've defined it using textual SQL; but you can work out the equivalent ERD if you need to, based on all the foreign keys (REFERENCES or FOREIGN KEY) already defined in the tables.
Table where relevant data for your associates
CREATE TABLE associates
(
associate_id INTEGER NOT NULL PRIMARY KEY, /* Probably should be IDENTITY */
associate_name TEXT NOT NULL,
other_data TEXT
) ;
Table where all jobs are described:
CREATE TABLE jobs
(
job_id INTEGER NOT NULL PRIMARY KEY, /* Probably should be IDENTITY */
job_description TEXT
) ;
Table where you assign which are the job/s that every associate should perform, and the average_daily_goal:
CREATE TABLE jobs_x_associates
(
job_id INTEGER NOT NULL REFERENCES jobs(job_id),
associate_id INTEGER NOT NULL REFERENCES associates(associate_id),
average_daily_goal DECIMAL(5,2) NOT NULL,
-- (job_id, associate_id) is the natural primary key
PRIMARY KEY(job_id, associate_id)
) ;
Table where you store the worked hours that every associate performed of every kind of job s/he is assigned to perform (this is, more-or-less, equivalent to your DayData, but with a column for associate_id, so that it can relate to it):
CREATE TABLE worked_hours
(
job_id INTEGER NOT NULL REFERENCES jobs(job_id),
associate_id INTEGER NOT NULL REFERENCES associates(associate_id),
work_day date NOT NULL,
worked_hours DECIMAL(5,2) NOT NULL,
-- The pair (job_id, associate_id) must exist in jobs_x_associates
-- we do not allow associates to perform jobs they have been not
-- assigned. Depending on business rules, you can lift this
-- restriction
FOREIGN KEY (job_id, associate_id)
REFERENCES jobs_x_associates(job_id, associate_id),
-- Only one entry per associate,job and day
PRIMARY KEY(associate_id, job_id, work_day)
) ;
Sample data for base tables:
INSERT INTO associates
(associate_id, associate_name, other_data)
VALUES
(1, 'Alice Cooper', 'nice buy'),
(2, 'Bob Geldorf', 'difficult to define') ;
INSERT INTO jobs
(job_id, job_description)
VALUES
(101, 'Cleaning windows'),
(102, 'Dish washing') ;
Sample data for jobs assigned and worked hours:
-- What should everyone do?
INSERT INTO jobs_x_associates
(job_id, associate_id, average_daily_goal)
VALUES
(101, 1, 5.0), -- Alice should clean windows for avg. 5 h/day
(101, 2, 3.0), -- Bob should clean windows for avg. 3 h/day
(102, 1, 1.0), -- Alice should wash dishes for avg. 1 h/day
(102, 2, 0.5); -- Bob should wash dishes for avg. 0.5 h/day
-- We record what they actually did
INSERT INTO worked_hours
(job_id, associate_id, work_day, worked_hours)
VALUES
(101, 1, '2017-07-01', 5.0),
(101, 2, '2017-07-01', 3.0),
(102, 1, '2017-07-01', 0.7),
(102, 2, '2017-07-01', 1.0),
(101, 1, '2017-07-02', 3.0),
(101, 2, '2017-07-02', 2.0),
(102, 1, '2017-07-02', 0.8),
(102, 2, '2017-07-02', 1.3) ;
With these data, you can perform queries such as:
SELECT
work_day, associate_name, job_description,
average_daily_goal, worked_hours
FROM
worked_hours
JOIN jobs USING(job_id)
JOIN associates USING (associate_id)
/* LEFT */ JOIN jobs_x_associates USING (job_id, associate_id)
ORDER BY
work_day, associate_name, job_description ;
work_day | associate_name | job_description | average_daily_goal | worked_hours
:--------- | :------------- | :--------------- | -----------------: | -----------:
2017-07-01 | Alice Cooper | Cleaning windows | 5.00 | 5.00
2017-07-01 | Alice Cooper | Dish washing | 1.00 | 0.70
2017-07-01 | Bob Geldorf | Cleaning windows | 3.00 | 3.00
2017-07-01 | Bob Geldorf | Dish washing | 0.50 | 1.00
2017-07-02 | Alice Cooper | Cleaning windows | 5.00 | 3.00
2017-07-02 | Alice Cooper | Dish washing | 1.00 | 0.80
2017-07-02 | Bob Geldorf | Cleaning windows | 3.00 | 2.00
2017-07-02 | Bob Geldorf | Dish washing | 0.50 | 1.30
SELECT
associate_name, job_description,
average_daily_goal,
CAST(avg(worked_hours) AS DECIMAL(10,2)) AS average_worked_hours
FROM
worked_hours
JOIN jobs USING(job_id)
JOIN associates USING (associate_id)
/* LEFT */ JOIN jobs_x_associates USING (job_id, associate_id)
GROUP BY
associate_name, job_description, average_daily_goal
ORDER BY
associate_name, job_description ;
associate_name | job_description | average_daily_goal | average_worked_hours
:------------- | :--------------- | -----------------: | -------------------:
Alice Cooper | Cleaning windows | 5.00 | 4.00
Alice Cooper | Dish washing | 1.00 | 0.75
Bob Geldorf | Cleaning windows | 3.00 | 2.50
Bob Geldorf | Dish washing | 0.50 | 1.15
You can check everything at dbfiddle here
NOTE: If you do allow associates to perform (and record) jobs they were not assigned to do, remove the FOREIGN KEY mentioned before, and change the /* LEFT */ JOIN to actual LEFT JOIN. This will allow the database to still show them, even if the average_daily_goal is N/A (i.e.: NULL).
