Database schema for timesheet app?

Question

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!

Answer 1

Your outline is exactly what you would want to do. If this was a real product the only addition I might add is that for the Timesheet table you would probably want your Clustered Index on the TimeStamp field as it sounds like one of the primary filters and you'll be selecting ranges from the table. If you are always searching by TimeStamp, Employee, and Employer key you can make the Clustered Index on all three.

A Clustered Index is the table itself on the disk, and it will be stored using the selected index as the logical sorting for the data. In most SQL servers when you create a Primary Key it automatically creates the Clustered Index on those columns, however it is not a requirement that they are the same columns (or even both need to be present) and can be on different columns. The primary reason to have the Clustered Index on other columns is to improve query performance on commonly searched data that is grouped by value or accessed via a range.

In general Indexing requires careful analysis of your queries to insure they benefit from all your indexing choices.