0

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!

Improve this question
1
  • You should not have an employee id as a FK in employer because an employer has more than one employee. Similarly you should have a separate person table that has attributes about each person you are tracking. The employee table should be a table that links a person to an employer and contains attributes about being an employee. The reason is because a person can be an employee of multiple employers.
    – Jim D
    Nov 26 '20 at 18:50
0

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.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.