Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Staff Duty Roster

Author  Topic 

Quentin
Starting Member

12 Posts

Posted - 2013-02-14 : 18:58:54
I am looking at transferring our current spreadsheet roster to a database. There are 5 parts to the data.

Staff Details - Firstname, Surname, staffid etc
Roster Details - rostertype (Onsite,R&R, Annual Leave ect)
Duty Details - each 3 months a persons duties are changed in a rotating system (Duties like Cleaning, Cooking, Maintenance etc)

I would like the output to be based on a date range so I need to assign each Day a roster type for each staff member, then a duty type for that date as well.

Is the best option top have a table for each detail (Staff, Roster, Duty) which includes a date range for each particular item and then a view central table for Date, Staffid, RosterType, DutyType).

Or is there a better way?

Thanks

markspend1
Starting Member

5 Posts

Posted - 2013-02-18 : 14:22:11
Hey Guys i think that the term of duty roster is an business unit that determines what and when people in a group need to do.Such company is important in a group process or objective because it guarantees that all necessary responsibilities are finished. It also allows affiliates to see that others have been allocated duties, so they do not think that they are the only ones doing the perform.Thanks in advance!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-09 : 04:59:58
quote:
Originally posted by Quentin

I am looking at transferring our current spreadsheet roster to a database. There are 5 parts to the data.

Staff Details - Firstname, Surname, staffid etc
Roster Details - rostertype (Onsite,R&R, Annual Leave ect)
Duty Details - each 3 months a persons duties are changed in a rotating system (Duties like Cleaning, Cooking, Maintenance etc)

I would like the output to be based on a date range so I need to assign each Day a roster type for each staff member, then a duty type for that date as well.

Is the best option top have a table for each detail (Staff, Roster, Duty) which includes a date range for each particular item and then a view central table for Date, Staffid, RosterType, DutyType).

Or is there a better way?

Thanks


I think you can use approach like this
Have reference tables to store STaff,Roster, Duty etc
Then have a table which relates all these to indicate the type of duty for a roster assigned to an employee with period (start and enddates) for the roster
ie fields as StaffID<RosterID,DutyID,StartDAte,ENdDate with all IDs joining to respective master tables via foreign key constraints

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -