views:

149

answers:

3

Hi guys,

i need to create an employee shift database. so i have 3 tables so far, employee, employee_shift, and shift

im suppose to calculate how many shifts an employee has done at the end of the month, my question means, because a month has 30 days some have 28 and 31 days.

this means i need to create in the shift table 31 different variations? one for each day of the month? in order to calculate which employee has worked the most?

in my business relation it says an employee has either 1 or 2 shifts per day therefore do i have to have 60 different rows of variations? im i right or is there an easy way to work it out

A: 

I would also stick to three tables, Employee, Shifts and Employee_InOut. The last table will have columns for date, in-time and out-time. The in-time and out-time will be the key to find out which shift the employee is doing. So I can just count the no of shifts along with shift details for an employee for a month. It will be simple.

Kangkan
No. YOu dont - and you should get someone help you designing, because if you try a 60 field table for that you basically are the blind trying to design a sql database. Ask someone in the company to help you. One who knows SQL basics. THen learn ;)
TomTom
Just go back to the basics of RDBMS and normalization. Putting so many fields will restrict your flexibility and just increase the complexity many folds.
Kangkan
A: 

I would use three tables like this:

Employee - EmployeeId, EmployeeName etc.
ShiftRecords - ShiftRecordId, EmployeeId, InTime, OutTime
Shift - ShiftId, ShiftStartTime, ShiftEndTime

If there are chances that shift times would vary, you can include shift start date and end date along with IsActive column in the Shift table.

danish
can you please create a select statement to show the emp with the highest shift for certain month
DAVID
A: 
  1. You cannot insert data into TimeStamp columns. Use Time instead.
  2. You cannot have Emp_Id and Shift_Id as the primary key since that would repeat. If you really need a primary key, add another column say Shift_Record_Id.

As far as creating the script is concerned, it should be simple. All you need is to figure out a logic and read about select statements and available keywords we can use in it.

danish