Employee Clocking in & Out System database

Question

What would be the best database design for employee clocking and out? Right now I have two tables.

Employee Base Table: Employee Id, relevant information like name and address, clocked in column

Employee Clocked in Table: Employee id, clock in date, Clock in Time, Clocked Out Time.

Is this a good way to track clocked in and clocked out? I appreciate any help

Answer 1

You may need a Work Schedule table to figure out if people are not clocking in when they should. For example:

Work Schedule Table

• Schedule ID
• Employee ID
• Date
• Shift Start Time
• Shift End Time

Answer 2

1) I assume "clocked in column" in employee table is for dynamically tracking whether an employee is clocked in? If so, I'd recommend a separate "clocked in" table instead - you would usually prefer employee table to be stabler.

2) Your schema does not specify if EID and date are unique in second table. Doing so would prevent people from clocking out mid-day, so take that under consideration when building indexes