We are currently developing a shift management application for part-time jobs.
I have a question about design for data measurement and browsing.

As a KPI,

  • Shift submission rate for part-time employees
  • Number of shift hours submitted by part-time employees this month, next month, and next month
I want to follow

And I would like to record and browse the number of hours submitted by each individual on a daily basis.

For example, the data I want to view

  • Is the submission rate (number of submissions/total number of submissions) higher or lower than this month last month?
  • Is there more or less submission time (total time) compared to the current month last month?
  • Is the person who submitted the next month last month, the next month is submitted (ratio and biname)


As a premise,

  • Employees can also submit the current month's shift time
  • Employees can submit up to the next month's shift time
  • Employees can change the submitted shift time

How should I collect data in such a case?
Consultation including table design.

I thought

ID name Number of submissions for the current month Submission hours for the following month Number of submissions for the next month Record date
XXX Y, 20 45 30 2019/11/27
Although it is data like

, I don't think it is very beautiful, and I think the problem is that the number of records increases every day by the number of part-time employees.

I would like to know what kind of design should generally be used in such cases.

Supplemental information

When using RDB, MySQL is assumed.

  • Answer # 1

    If it's a question, the recording date is the current month.
    ・ It is troublesome to distribute the next month.
    ・ If there is a previous month's amount for the next month and there is no report for the current month, it must be traced

    (Employee ID,working date, submission hours, record date)
    * The bold text above is the primary.
    If you want to record all submissions, you must add the recording date to the primary.

    If the DBMS can use the analytic function lead () for the above data, it is not troublesome to get the next month or the month after next.
    However, considering that there is no submission, data for the standard year and month is required.

    If you use postgres, you can use generate_series and you can also use analytic functions.