Case Study: The development of a Schools and Colleges Database for the Department for Education

We were asked by the Department for Education, following our database publishing work on their School & College Performance Tables, to develop and manage their first mailing database of schools and colleges.

Our first question was ‘How have you operated in the past?’ There was a database on a mainframe, from which mailing labels were produced periodically in blocks. There were two major problems with this: 1) Given the frequency with which schools’ details changed, especially schools closing, opening or amalgamating, the data was not exactly up-to-the-minute; and 2) it was difficult to write to a set of schools for which a label set did not exist.

ABC therefore assembled every piece of data within the department that described and defined schools and from this created a database of approximately 25,000 schools and FE colleges. We then began to build a list of all the ‘queries’ or selections that departmental staff might want to throw at it. These ‘outputs’ were fed into a schools classification structure that enabled all the relevant code data to be assigned to each record. This illustrates a core principle of database management, that ‘outputs drive inputs’.

So even though you can respond to 99% of all queries on Day 1, output requirements are never static. New ways of selecting data are continually needed by the department, leading to the coding structure needing to evolve to accommodate these. So if a new requirement presents itself, say a new school type such as Beacon schools, then you merely add new fields and populate these as necessary. Another principle of all successful databases is that their development is evolutionary, rather than revolutionary. Put another way, they adopt a step-wise, rather than a ‘Big Bang’ approach.

A key aspect of this database, as with so many others, is that if the data is in there somewhere, we can get it out! So no matter how complex the selection requirement, or how convoluted the output requirement, the results need to be made available as accurately, but usually also as quickly, as possible. Also if a mailing house has a particular formatting requirement, this must be taken into account and the output structured accordingly.

top of page

Perhaps the dataset is for publication? Or perhaps the need is to extract only records that meet a certain criteria, regarding size, performance or ages taught. How about inviting to a conference the Heads of all secondary schools with more than 300 pupils within sensible driving distance of Swindon. And so on.

Another key requirement was that data needed to be viewable on PCs within the department. Thus ABC developed, in conjunction with the client, a database ‘front end’ that enabled records to be pulled up on the screen based on a wide range of identifiers, usually within a second or less. This data therefore needed to be kept up-to-date, and a regime of uploading via Internet file transfer was devised. Commenting on the flexibility of the set-up, Ron Gibson of the DfES’s Performance Tables team, said: “Schools are changing all the time. New types of schools, such as City Academies, are being added all the time, yet the database is capable of accepting these structural changes, usually within minutes. It is therefore difficult to imagine how we would have undertaken many key exercises related to the preparation of each year’s Performance Tables without a system as easy-to-use and as foolproof as this in place.”

The bottom line for this project was:

• the data had to be as accurate as humanly possible, since the Performance Tables were derived from it
• it had to be capable of responding to a wide range of queries, called off by staff that ranged from the IT-literate to those who were less so
• it needed to be prepared in virtually any data format, and output either electronically, or as hard copy or laser labels; data transfer links have also needed to be established with, over the years, dozens of different mailing houses, conference organisers, educational bodies, other government departments and NDPBs.

top of page