Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

employee work shift table design

Posted on 2005-03-26
11
Medium Priority
?
960 Views
Last Modified: 2006-11-17
Hi,

Please kindly guide me how to design the right table for employee work shift database. Which will be use to query their Break Adherence(lunch break in/out), total worktime and etc.

-[this is a weekly schedules]-
-[a shift is 9 hours long per day]-
-[an employee has 5 working days for a week]- (can be any of the day, mon,tue,wed,thu,fri,sat,sun)
-[there are three breaks per shift]-
-one lunch break for 1 hour.
-two 15 minute breaks.

-[schdedule can change for tomorrow but not yesterday]-
-[every half-hour there are employees coming in (no shifting)]-

i.e.

employee A's shift for today:
start: 7:00 AM
break: 9:00 AM
lunch: 12:00 PM
break: 2:00 PM
end: 4:00 PM

So far i'm stuck on this design (I think we need to place a Date and Time stamp value inorder to query historical schedules)
Please kindly guide me how to finish the design below

Thanks.

-[Operator Shifts]-
empoyee ID
Shift Date
Shift ID
Schd Txn Seq No

-[Schedule Master]-
Schd Txn Seq No
Break1 Time
Lunch Time
Break2 Time

-[Shift Master]-
Shift ID
Shift Name
Start Time (start time + 9 hours = end time)

-[Weekday Master]-
Day of Week ID    (1 for sunday, 2 for monday, 3 for tuesday, 4 for wednesday, 5 for thursday, 6 for friday, 7 for saturday)
Day of Week Name
0
Comment
Question by:lynnton
  • 6
  • 5
11 Comments
 
LVL 17

Expert Comment

by:mokule
ID: 13636282
I think Weekday Master is unnecessary as You can get it using
DATENAME function. for example
SELECT getdate(), DATENAME(dw,getdate())

Is it possible to start shift on one day and end on the next?
What is Shift Name?

mokule
0
 
LVL 1

Author Comment

by:lynnton
ID: 13636304
mokule,

Yes, it is possible to have a start shift one day and end on the next.

I'm not also sure what is shift name, since this was pass on to me.

Can you kindly help me create a new design, better one with table names and columns?

Thanks.
0
 
LVL 17

Accepted Solution

by:
mokule earned 2000 total points
ID: 13636362
The simplest possibility
------------------------
Employee -Table
ID       int
Name  varchar
------------------------
Shitfts - Table
ID        int
EmployeeID int
Start           smalldatetime
Break1Start           smalldatetime
Break1End           smalldatetime
LunchStart           smalldatetime
LunchEnd           smalldatetime
Break2Start           smalldatetime
Break2End           smalldatetime
End           smalldatetime
------------------------
You may not use end times for each break if it is not necessary.
You may also use smalldate field for Start only and the other fields smallint for time duration from Start time

mokule
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Author Comment

by:lynnton
ID: 13636426
mokule,

With that design we would repeat couple of data..

Shifts table
ID
EmployeeID
Start
End

What could we do?

Thanks.
0
 
LVL 17

Expert Comment

by:mokule
ID: 13636455
Wait a moment. Maybe I don't understand something.
Is times for breaks and lunch the same for all employees or each employee has its own ?
0
 
LVL 1

Author Comment

by:lynnton
ID: 13636498
mokule,

Breaks do very for every employees (each employee has its own).
start time is limited to 24 hours (possible combination is 48 since it's every half hour).

Where will we place date stamps?

time stamps are in
start time
break1
break2
lunch

Thanks.
0
 
LVL 17

Expert Comment

by:mokule
ID: 13636512
Do You want to control (register) breaks, lunch and shift durations?
0
 
LVL 1

Author Comment

by:lynnton
ID: 13636517
mokule,

Yes, register shift breaks for break adherence report.

break adherence report is: did the employee adhere to thier breaks for today ? did he comein late? over break? etc

Thanks.
0
 
LVL 17

Expert Comment

by:mokule
ID: 13636571
So my first suggestion seems ok
If You don't need to register exact time start and end of breaks, lunch and shift You may use this table for temporary purposes.
When the shift ends tha data may be rewritten to table so historical data will be more compact

Shitfts2 - Table
ID        int
EmployeeID int
Start           smalldatetime
Break1Duration      tinyint
LunchDuration        tinyint
Break2Duration      tinyint
ShiftDuration         tinyint
0
 
LVL 1

Author Comment

by:lynnton
ID: 13636629
mokule,

I got it, the new system parameter table will tell us the duration per shift (9hours), break duration (15 minutes), lunch duration (1 hour) and etc.

Please take a look below; i've made a sample data..let me know if there are any flauws.


-[system parameter]-
parameter name
parameter value


-[Operator Shifts]-                  
empoyee ID Shift Date Shift ID BreakID
1                  1/1/2005      1      1
2                  1/1/2005      1      2
3                  1/1/2005      2      3
                  
-[Schedule Master]-                  
BreakID Break1 Time Lunch Time Break2 Time
1              10:00            15:00        19:00
2              11:00            16:00        20:00
3              12:00            17:00        21:00
                  
-[Shift Master]-                  
Shift ID  Start Time (start time + 9 hours = end time)            
1            08:00            
2            09:00            
....will contain only 48 records (all the possible start time)
0
 
LVL 1

Author Comment

by:lynnton
ID: 13636688
mokule,

I got a flaw, since we only tag date once, what will happen for those shift that will go to next day?

how can we resolve this?

Thanks.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question