I am very new to database managements as I am enrolled in my first class involving databases at university. For my project, I have to create an app that clocks employees in an out and keeps track of hours they worked. There are many employers who each employ many employees who each can technically clock in and out an unlimited number of times daily. My goal is to be able to easily select an employer, get all his/her employee, and be able to make a table for their hours for any given week. Here's how I was thinking to design the schema:
Employer
Employer id
Employee id (FK)
Name
Employee
Employee id
Employer id (FK)
Name
Password
Timesheet
Employee id (FK)
Employer id (FK)
Timestamp
A boolean indicating whether the employee is checking in or out
So every time an employee checks in or out I put an entry to the timesheet table. Then to grab a week, I grab all the timesheets for an employee within that week's range and compile them into a table. This doesn't seem to be the most efficient way, but I am not sure of a better way.
Any help is very much appreciated!