Weekly timetable design - general advice

[bister]

Hello,

I'm a relative novice trying to create a basic timetable management solution for a suite of operating theatres in a (resource-poor) public hospital as well as a job plan for each anaesthetist.

My main question is whether to display a weekly timetable I should concentrate on using sorted, filtered portals or or whether I need to manage the horizontal nature of the timetable by using 10 table occurences accessed by relationships using a global "Session in Question" field and 9 related fields incrementing by one each time to display the 10 sessions that make up a working week.

The main parameters of the problem are:

- we have a suite of theatres which run operating sessions morning and afternoon on weekdays

- each theatre has a speciality for the session, a surgeon, an anaesthetist and one or more trainees

- we have a rolling template roster which runs over 4 weeks and which changes over time. (But revisions have a date from which they will become valid)

- we need to generate an actual weekly timetable from the rolling template a couple of weeks before the week in question

- I also need to account for non-theatre time for the anaesthetist i.e. on the rolling timetable: sessions spent in clinic, doing non-clinical work, regular days off etc.

- For the actual timetabIe need to account for anaesthetists who are on leave.

The data design seems relatively straightforward

- there is a unique record for each session (week,day, time of day) for each theatre or unique clinical activity. Fields are SessionID, Location (= theatre), Speciality, Surgeon, Anaesthetist, Trainee

- I've treated Office time and rostered days off for the anaesthetist as similar records, but with Location set to "Office" or "Away". In this case there is no speciality or surgeon, but the Anaesthetist field refers to the anaesthetist.

- I plan to manage rolling roster revisions with a ValidFromDate and ValidTo Date for each record.

The actual timetable as opposed to the rolling template will basically be similar and generated by lookups from the rolling roster. It will also include leave management.

My two main options seem to be:

1) Create 10 portals for the theatres (15 rows each), 10 portals for non theatre activities(4 rows each) and 10 portals for each of: Office time, Rostered off time, Available. There need to be 10 portals laid out horizontal for each of these to display Mon-Fri am/pm sessions. I can ensure that each portal displays the appropriate records by filtering, sorting and using the appropriate starting row number in the portal definition.

2) Use a similar layout of portals, but use different Table Occurrences of the main table that rely on relationships defined by globally stored fields: gSession01, cSession02 (=Session01 + 1), cSession03 (=Session01+ 2) etc... i.e. use a series of 10 sequential keys to access records related to 10 sequential sessions. This will reduce the amount of filtering relationships required for the portals, but by no means eliminate them.

I'm sorry if the above discription is too complicated, so feel free to TL;DR me. But in essence I need to know if performance relying on table occurrence relationships is likely to be bearable and the solution more robust, or if using portal filters more extensively will provide adequate performance. The overall number of records is likely to be

365 days per year x

2 sessions per day x

(15 Unique locations + 12 records for locations which have multiple assigned anaesthetists (i.e. (anaesthetists in the office ~ 3 records) + (Available anaesthetists ~ 3 records) + (anaesthetist on leave ~ 6 anaesthetists)).

so approximately 10,000 records for the actual timetable per year (although I only ever need to work on an active subset of one week's worth of these records at a time) , and approximately 2,000 records for the rolling timetable plan.

Any general advice as to which avenues I should explore would be very welcome!

Micahel

[comment]

I'm afraid I can't follow this very well (what's a "rolling template roster"?). I'd suggest you study these:

http://fmforums.com/forum/topic/34417-field-count-calculation/page__p__159796#entry159796

http://fmforums.com/forum/topic/42617-basic-monthly-calendar-april-06/

[bister]

Thanks for the advice. Sorry the explanation was too convoluted. I'll look at the examples you give though and I think they may put me on the right track. Already I think I understand I need to structure my data properly THEN work out how to display it

Michael

[Vaughan]

Already I think I understand I need to structure my data properly THEN work out how to display it

Some people NEVER grok this. :D