A database to record, track and report health student rural placements

full article:

The Spencer Gulf Rural Health School (SGRHS), South Australia, is a combined University Department of Rural Health (UDRH) and Rural Clinical School (RCS), funded by the Australian Commonwealth Government to provide health education in a rural environment to students from its affiliated universities (University of Adelaide and the University of South Australia). The RCS has the specific target of ensuring 25% of Australian-origin medical students receive 50% of their clinical training in a rural site¹. The SGRHS, like all UDRH and RCS, is required to regularly report detailed information to the Commonwealth on students supported through the funding.

To provide this rural education, the SGRHS has established 'learning centres', recognized rural cities or towns that provide both academic (program coordination and curriculum delivery) and logistic (orientation, accommodation, IT access) support to students studying there. Each learning centre has SGRHS academic and/or administration staff to facilitate student placements. Each of these staff members is required to keep up to date with student arrival and departure dates, and to coordinate orientation, accommodation and transport bookings.

The SGRHS required a database system to track and record student placements both for internal staff use and to generate Commonwealth Government reports. The database needed to be accessible to staff at multiple sites, and to satisfy the needs of both academic and administrative staff. Because UDRH and RCS are a relatively new endeavour there were not existing databases available to meet these needs (existing databases were designed to allocate students in an urban environment, hence they did not take into account the logistic requirements of rural placements). An existing database application utilised by the University of Adelaide and Flinders University of South Australia (Rural Undergraduate Medical Placement System (RUMPS)) could not be utilised because accessing the database remotely required considerable hardware upgrades to the residing server. Other impediments to RUMPS were that the connection type utilised low bandwidth solutions (direct modem access), and the database schema was not ideal for SGRHS purposes, hence it would require additional coding. RUMPS was also designed in a specialist programming language which few people in Australia had the capacity to alter.

This article presents the process and outcome of the design and development of a relational database, accessible in real time from multiple sites, utilising the internet as a vehicle to track and record student rural placements.

Concept

The design of the database was a two-stage process. The first phase was to develop the content and structure, the second to determine the format of the 'public face' of the database where staff could view and interact with placement records and generate reports as required.

Potential users were consulted on their database requirements (Table 1). Users included the Commonwealth (standard RCS and UDRH reporting requirements), and the SGRHS executive team, program managers, academic and administrative staff. This consultation highlighted the importance of the database being accessible from numerous locations, for the data collected for each placement to fulfil multiple (often dynamic) reporting requirements, and for it to fulfil the logistic 'tracking' requirements of administration staff (for example, accommodation and transport bookings).

Table 1: Database requirements identified by potential users



The database was constructed in Microsoft (MS) Access, based on identified content requirements, in a relational indexed format, linking primary and foreign key structures within the tables to join relevant fields for data manipulation. MS Access was chosen on the basis of the current skill set of IT staff, and due to the size of record structures remaining minimal for a considerable period of time. Porting from MS Access to MYSWL could be implemented if the system or add-on functionality required it.

A web-based interface was designed in dynamic ASP with VBSCRIPT as the chosen coding language. This interface allows users access to enter, edit, view and print data in the database via the internet. The interface has two levels of access. Regional and local administration staff are able to access the first level of the database only. Here they are able to view and edit all student and placement information, and enter new details of accommodation bookings. Central administration staff and placement coordinators have the same access to the first level of the database, but also have access to the second password protected 'administration' level. Within this second level, users are able to add or delete students, placements, supervisors and host facilities.

Security of the database contents was of major concern to SGRHS and parent university departments. Because of this, three layers of security were installed:

1. The Student Database can only be accessed from the SGRHS 'Staff only' section of our website. This 'Staff only' section required network user authentication in order to proceed to log onto the database.
   
2. The Student Database has internal password access, not all SGRHS staff have access to the database.
   
3. VB coding has been utilised on all web pages so that no HTML address guessing or spoofing will allow users to enter and access the database, they must authenticate onto the system in order to view any information.

After logging into the web interface, users are greeted with a welcome page (Fig 1). The top navigation bar allows access to four interface components. The first is a personalised section allowing each user to see 'my info', a list of information on placements in their region for a selected month that is automatically generated from their username. The second allows users to 'search' for a student and their placements by first or last name. The third allows selected users to log into the 'Administration' section already described. The final component ('Report') allows users to select particular reports for statistical or reporting purposes (Fig 2). Once a student and placement are selected (either through 'Search' or 'My info'), users are able to view, edit (Fig 3), add or delete details relating to the placement or student demographics by clicking on items in the left navigation bar. This allows users to navigate around the sections of the website in an efficient manner. The left navigation screen remains visible regardless of the area the user is accessing, ensuring they will be able to access other sections with a single mouse click.



Figure 1: Database welcome page.




Figure 2: Database generated Commonwealth report.




Figure 3: Student demographics edit page.


Outcomes

The student rural placement database has been utilised as the primary tool to record and track student placements in the Spencer Gulf Region for 2 years. The database was operational at the start of the academic year for the first time in 2003. As the SGRHS allocated or were informed of student placements they were immediately included in the database, streamlining the administrative process for new placements.

After initial 'teething' problems the database is now functioning well and a second stage of development is in progress to increase reporting functions, review underutilised functions and incorporate changes to UDRH Commonwealth reporting proformas. These teething problems related to both technical and usability of the database. Technical problems included incorrect process of adding updates through the website leading to a loss of data integrity and problems with finding and accessing an appropriate web server.

Following installation and database training, a feedback session was held during a staff meeting to discuss usability and navigation issues. Due to the geographical location of staff it was initially unclear as to who had responsibility for entering data into the various sections of the database. Information on each student placement had traditionally been distributed on paper, meaning a number of different staff members were informed of new placements and, hence, wanted to enter them into the database. The development of a flowchart that indicated the flow of students both through the database and the SGRHS as a whole enabled a reduction in double handling of placement information. This flowchart was distributed to all relevant SGRHS staff and to placement officers from the sending universities.

When the database first went online, access to editing and adding new information to the database was restricted to those with 'level 2' access with the view to maintaining data integrity. Onsite student support administration staff were often provided with updates on student contact details and placement activities while the student was on placement. Their feedback on this resulted in 'level 1' access increasing to allow editing all areas of the student placements.

As with any database, the integrity of the data within it relies on the accuracy of manual data entry. All database users undergo a training program to introduce them to the structure, use and capabilities of the database. A secondary phase of the database will allow placement information from sending university schools to be uploaded directly into the database, reducing human data entry error.

We envisage a tertiary development stage of the database will allow password protected access to students for checking and updating personal and placement information, obtaining information about logistic bookings made for their placement, and contact information for supervisors and organisations. Access through this section will also provide students the opportunity to self-select a placement location from a pre-determined list.

Future analysis of the database will allow the 'tracking' of rural placements for individual students. As cohorts of students are followed through there will be potential to analyse the effect of rural placements early in the curriculum on the students' decision to participate in later elective placements.

The Student Rural Placement Database could be readily packaged and installed at a different location hence, with few alterations, could be utilised by other RCS and UDRHs in Australia who have the same reporting requirements as the SGRHS. The database could be purchased by other schools as a package including a manual and introductory training. With modifications and additions the database could be adaptable for schools outside Australia.

Acknowledgements

The authors wish to acknowledge the following people: Christopher Battye for initial database engine build, graphical web design and ASP coding; staff from the Teaching and Learning Program of the SGHRS for database content and testing input; and Jonathan Newbury for manuscript proofreading and advice.

Reference

1. Department of Health and Ageing. Workforce Education and Training; Rural Clinical Schools. (Online) 2002. Available: http://www.health.gov.au/internet/wcms/Publishing.nsf/Content/health-workforce-education-clinical.htm (Accessed March 2004).