1

So, I'm not a database expert (or really have much experience at all other than my college classes) and I need someone to point me in the right direction on how to design this problem. To be very clear, I'm out of school and I have my degree. This is NOT a homework problem.

I work as a software engineer at a company that manages other company's warehouses. I built a system that tracks our associate's productivity by the hour and I need the information to be stored in a database. Previously I have used Java and simply serialized classes and stored the data in files on our server but the problem is I've had to change so many data types to account for more information we wish to track and I lose the serialized objects after any significant changes. Anyways, I decided to just use a database to store this stuff but really am having a hard time with it's design.

So the information I need stored is as follows...

  • Associates

  • Jobs (Associates can work multiple jobs throughout the day and each job has a different goal average they need to reach)

  • Each Associates' Total Work Hours for a given day

  • Need to save all this for each separate day (this is the hardest part for me and I have a mental block on how to separate all that by day)

A little more info: The way I designed this in code was like this.

HashMap<LocalDate, HashMap<Employee, DayData>> data = new HashMap<>();

So as you can see each day has a list of employees each with a "DayData" object associated with them.

The DayData object contains total day work hours and a list of WorkJob.

WorkJob stores the name of the job, the unit per hour the associate hit, and the units the employee packaged.

I hope this is clear as mud. I can explain anything further if needed. I really need someone to point me in the right direction with this. Please and thank you.

3

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).

  • Wow, this is way more detailed than I expected! There are two issues I see. The first is every job has a static goal average that is the same for every associate (it doesn't change on any given day unless we up the goal). With that I figured on just not using a jobs_x_associate table and storing the job goal average in jobs. The other issue is the more serious one. The worked hours is the total work hours in Kronos and not all the jobs added together (I'm sorry I didn't define this in my question) (continued)... – Philip Vaughn Jul 13 '17 at 14:00
  • The time spent on a job is auto calculated from "units" divided by "units per hour" so there has to be a place to store those two amounts for each job. These are by the job as opposed to "total work hours" being just simply hours they were on the clock that day for each associate. – Philip Vaughn Jul 13 '17 at 14:04
  • ... I think you have already a good starting point. If the "units per hour" depend on the job => put them in the jobs table. You have to be able to find out to which entity each piece of information refers to. In any realistic scenario, you very rarely have the same goals for every associate (or worker). If that's what's happening to you right now, it is probably an exception. This will change as soon as you have enough associates. Anyhow, adapt to your needs. This was a first approach based on my understanding of your description. – joanolo Jul 13 '17 at 17:30
  • I think this has solved my issue. I really appreciate the input with this. I built my tables and I'll test it when I next get the chance but your answer certainly pointed me in the right direction thank you. – Philip Vaughn Jul 13 '17 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.