How should I design data tables for time attenedance and payroll system for complex shift work?

[Asa_Wu]

Hello,

I am creating an app for time attenedance and payroll system for my work place which has a very complex shift work. I would like to ask your help to suggest me idea how should I design data table structure and its relationship.

The criteria of my workplace are:

1. Many different shifts, for example, some works at 8:00-16:00, other 8:00-20:00.
2. There are cross-day shifts, for example, 20:00-8:00 of tomorrow.
3. Many break times between shift, for example, break times at 12:00-13:00 and 17:00-18:00 for 8:00-20:00 shift. Worker doesn’t need check fingerscan during break time if not go outside workplace.
4. Worker can have multiple shifts in the same day and need to checkin&out fingerscan.
5. Raw time data are collected by fingerscan. Worker have to check fingerscan every time they go-in or go-out workplace.
6. Worker shift can be changed during week.
7. All collect data is required to calculate working hour ,OT, and payroll.

In addition, Should I have two fingerscans: one for check-in and another for check-out, for distinguishing check-in and check-out timestamps in a different spreadsheet? Or It doesn’t need. Can they be distinguished by app design?

Thank you very much.