?
Solved

employee work shift table design

Posted on 2005-03-26
11
Medium Priority
?
800 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

764 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