Home>
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
.
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 |
, 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 informationWhen using RDB, MySQL is assumed.
-
Answer # 1
Related articles
- about database design
- database design - for example, in a program that displays inventory
- What are the constraints of database design integrity?
- Java design pattern-static proxy mode (comparison of aggregation and inheritance)
- Linux implementation of mysql database daily automatic backup scheduled backup
- Detailed MySQL database design using Python operation Schema method
- Linux daily backup database regularly and delete data ten days ago detailed steps
- database design - does the structure of the db when creating the comment column separate the comment and the reply table to the
- database - db design, need for guid
- mysql - about database design and implementation
- database design - do you want to put nlll in the table by default?
- improved sql database design
- database design - should the db in the staging environment be the same as the production environment?
- mongodb - about the database design of the two-choice question app
- django - about database design
- mysql - about database design
- vba - [database design] about table design when referencing/updating the access database using excel as an input screen
- Daily collection and arrangement of SqlServer database optimization experience and precautions
Trends
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.