Carrubbers
Christian Centre
The majority of the focus for the rota is the concept of a service. We already have a services database, so it makes sense to store rota information that relates to a service in that table to save us from having to do lots of "Join"s on tables. The system itself already contains the service chairman rota and the preaching rota, so it's really just an extension of that.
However the majority of the data in the rotas subsystem will not be of interest in the context of the service planner, so it would not be beneficial to store this information in the service planner itself so we will have an explicit rotas section, which will update information in the services database.
We need an index into what duties there are, so we will have a Duty database which gives meaning to the "DutyId"s. Each Duty can have a friendly name, and information to describe for which services the duty is required. Also information about who can edit the rota, by default rotas will be editable by team members and administrators. Also the email address to mail to when the rota changes, and the name of the rota owner.
Note that when we have implemented email addresses and lists properly, this address would be derivable from the duty name. ie the sound rota would go to sound@teams.carrubbers.org etc etc
In order to record availability, we will have an availability table, which will specify information about who can't do what at which services. By default all team members will be available for all services, but if a user can't do something in a service then they would add a row to the table specifying the service id, their name, and the duty id that they were unable to fulfil where the duty id of 0 would mean unavailable for all roles.
When you are authoring or viewing a rota, you will want to know who is in the team to select from for the rota. This leads us to a team members database, where each row represents someone's membership of a team. The row would contain fields for the duty id, the team member id, their name, and for which types of service they are able to fulfill the duty.
Note that when we have implemented email addresses and lists properly, this database would be usable to construct the mail list membership for a given team. However not all ministries will have a duty associated with a service, so the master membership list for each ministry probably will be held elsewhere and that can be used to build the membership list instead. This table is needed however as the assignment of team member id to resource should never be invalidated.
The above discussion leads us to the following database schema:
| Field | Type | Description |
|---|---|---|
| Name | String | The friendly name of the duty |
| IdName | String | The name of the column in the services db that contains the rota for this duty |
| Leader | String | The name of the person who's responsibility it is to author this rota. |
| Services | String | An XPath search to use against the services database to specify which services this duty is required for. If this is blank, or "true()" then that means it applies to all services. If this is "false()", then the duty is not required for any of the given services which effectively expires the rota. Note that the services databases which describe the location and if communion is on, so there is quite a lot of flexibility here and further service meta info could be added later. |
| EmailAddr | String | Comma separated list of email addresses who should be notified when this rota is toggled from draft to completed. |
| Status | Number, 4 bits | One of:
|
| Field | Type | Description |
|---|---|---|
| DutyId | Number, 8 bits | The Id of the duty whose team this person is a member of |
| MemberId | Number, 8 bits | The Id of the member in that team. The combination of MemberId and DutyId is unique in the table. MemberId's should be in numerical order. |
| FullName | String | Full name, unique in the table |
| OnRota | Bool | TRUE (1) if the user is currently active on the rota, or FALSE (0 or unset) if the user is not currently available to serve on the rota |
| AvailMp | Number 64 bits | Bit mask which specifies which services the user can serve in:
Note that this list and order should be the same as the ServiceType field of the services database. So for example if it had the value 5 for someone, this would be 0x4 & 0x1 = 4 and 1 = Sunday Morning and Saturday Evening. By setting this to zero, it specifies that the member is not available for any of the given services which effectively means the person is not currently part of the team. By setting it to 0xffffffff it specifies that the team member is available for all required services. |
| Field | Type | Description |
|---|---|---|
| ServiceId | Number | The Id of the service whose this availability request applies to |
| DutyId | Number, 8 bits | The Id of the duty whose team this person is not able to do. 0 means they can't do anything at that service. |
| FullName | String | The Full name of the member who can't serve at the given Service. This must be the same as the name in the Team Members database. |
To design the gui, it will help to think about the sequence of events that each user will have with the system.
When the rota author logs in they will want to view the next 3 months worth of services. They may be responsible for multiple rotas, but it might be nice to have a "View My" style feature to highlight which services they have specific responsibility for.
After selecting the rota they wish to author, they should be able to easily assign team members to services, respecting the restrictions outlined in the availability database. After finishing the rota, perhaps over multiple sessions, they will want to notify the team by using an "email rota" feature.
As team members come and go, they will need to be able to add a member to their team or change their settings, so there should be a membership section alongside a given rota.
The team member will want to primarily view all the services where they have been assigned duties. This may span multiple rotas and will refer initially to future services, next 3 months, although they may wish to move the time window.
They will want to login and view/print rotas and service teams.
The initial focus of the rotas sub-system will be a table, with services down one side, and duties along the top. Then the name of the person who is scheduled to fulfill that role in each cell. The list of services comes from the services database, taking the next 3 months of services, with the list of duties along the top coming from the duty database.
If you select a specific rota, then you would open up that specific rota and it would switch to a services x resource view with radio buttons to indicate who is rota'ed for each slot. If you are a member of that team, then the rota would be writable to you, and you'd be able to update the assignments but if you were not on that rota it would be read only with the controls greyed out. The list should also indicate when someone is not available to that rota, and also describe "unassigned".
If you are a member of several teams, then you would also want to have a "view my" option, which would show services x duty, and use read only checkboxes to specify when you are rotaed for what duty. It may not be valid to say that if you are rotaed to do one thing in a service, then you can't do something else.
It would be helpful too if there was a concept of a draft rota, and a completed rota, where we auto mail when the rota is changed from draft to completed. The rota author would set the rota to draft, then make changes, then when they were finished it would go back to completed, be flagged as read only, and mail out to the team. If a team member wishes to do a swap, they would have to toggle the rota to draft, make a change, and then "Email Rota" their changes round the team. Lets implement this last though, as it may be more of a hassle than a help.
For each of the views, we will presume that the rotas are authored in 3 month chunks, but we will provide the facility to move the window forward and back by a week at a time, or extend to a configurable number of weeks. Equally they should be able to "list all" which will show all defined services that have rota information for the selected duty or for all duties.
When printing or emailing a rota, they will not want the full menu banner, so we should provide a "pop up" window with a version of the rota that is suitable for printing, including a disclaimer footer to describe that the online version should be treated as the master copy, and all changes should be applied to the online version.
We will provide external links into the rota system to provide a more specialised "View Service Team" format, which again can be printed. This should be available directly from the services list and also from the service plan.
The above implies a gui where the default page shows a three month view of services. From here you'd have actions to:
When viewing a specific rota, you could move to a team membership page where you could:
Then with a specific rota selected, mostly if the rota was in draft, and you were a member of that team then you'd want to be able to:
As for above but:
