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.
| Author |
Topic |
|
Dinnertable
Starting Member
2 Posts |
 Posted - 2012-03-27 : 18:47:35
|
| Suppose you are to plan the schedule of employees that are assigned to specific tasks. But who sometimes can be away on vacation. Several workers can use one workstation, but not vice-versa (workers N:1 workstation).Workstation time interval assignments can be expressed by a table with:(workstation_id, start, stop, primary key)An employee assignment table can express who works where by using the primary key from the one generated in the workstation time table:(employee_id, primary key)The tables for employee and workstation descriptions is deemed out of the scope of the problem definition. Start-stop time interval is defined with seconds (epoch).To express vacation times one can create a table with:(employee_id, start, stop)Such that when the schedule planning takes place. The vacation table is first checked before assigning to a workstation. However an alternative is to create a special workstation_id that really is vacation.Using a separate table for vacation may reduce operations on the workstation assignment time interval table and as a bonus possible corruption issues from bad client code. On the other hand a common workstation_id table reduced the number of tables needed and requires less code. Any ideas for the most "pretty" way of doing it?Any new assignments in time is accomplished by splitting the time interval and reassigning the primary keys to the appropriate employee. Maybe there's a smoother built-in time interval handling way of doing it? as opposed to dealing with it with application side code. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-03-27 : 19:39:51
|
| I would go for separate vacation table as thats more sensible approach. You can maintain it as seperate entity and use it for wherever you need to consider the vacation status.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2012-03-27 : 19:58:15
|
| I agree with visakh16 regarding the vacation table. It just makes sense (to me).But you may also benefit from a [workstation availability object] such as a calculated table/view/udf. This may help to encapsulate the logic for determining what workstations are available and when, based on assignment scheduling and vacations.Are you attempting to reach maximum utilization from minimal workstations?If so, do you intend to split a single assignment across 2 or more worsktations based on workstation availability (i.e. assignment A on workstation 1 from time t --> t2, then assignment A on workstation 2 from time t3-->t4.How frequently are assignments made/vacations scheduled?What mechanism is used for crud (ie is there going to be an interface for assignment and vacation scheduling)?Is it transactional or batch processed (i.e. do you want real-time scheduling confirmation or is there a process run at sum regular frequency to assign)?Just some thoughts/questions I was pondering about the solution. They may or may not be relevant to the OP or the problem/solution though.Have a nice evening. |
|
|
|
Dinnertable
Starting Member
2 Posts |
Posted - 2012-03-27 : 20:36:22
|
| It's rather the other way around, which employees are available. The workstations must be manned at all scheduled times. Possible with some warning if a workstation isn't fully manned. Like patient without life support ;)I'm attempting to fill the manning of the workstations at all times.Vacations etc, are scheduled once a month or in such time interval. However I intend to fix such that one can call in sick etc, and then the software will schedule or call in another employee.The crud will most likely be a web interface (html/http).Batch processing for initial setup. And real time for "shit happens" when people get sick or other things happens that makes them unavailable.Some details on employee availability and the most "pretty" way to design the tables remain. But I think I have the basic design pretty clear such that anything else is just extensions which can easily be changed later without needing to do a basic redesign. |
|
|
|
|
|
|
|
|
Separate table or use special identifier number?