Introduction – Coaching Ireland CoachingDB

Coaching Ireland is responsible for educating and supporting coaches and providing services to players and athletes in the context of the Irish Sports Council Policies, with a view to improving the standards of coaching and performance. They are responsible for maintaining an accurate database of more than 30,000 sports people and use this to send out regular correspondences, run professional development courses and answer queries from sports people on their qualifications.

Problem – Legacy Database

The Coaching database had evolved for more than 8 years on a Microsoft Access database. The quantity of data stored in the Access database had outgrown the capabilities of Access and the system was only available to Coaching Ireland staff on their internal network. Coaching Ireland wanted to upgrade this database to a more reliable platform and make it available via a Web interface.

Challenge – New Database, Old Data

When the Access database was first designed, it had a very narrow set of requirements. Over the course of 8 years, the database structure was slowly changed to handle more and expanding requirements. This slow organic growth resulted in many data consistency issues. The upgraded Coaching database needed to be redesigned from scratch to adhere to all modern best practices, but a key requirement was that all data from the old Access database had to be ported to the upgraded database and available on the system from the first go-live date.

Solution – Iterative Import Process

Microsoft SQL Server 2005 was used as a foundation for a much more scalable database. The Coaching database structure was redesigned to take into account best practices and normalization techniques. The 12 tables of the Access database were normalized into 40 tables for the SQL 2005 database.

A C# application was written to read all data from the Access database, validate it and import it into the SQL 2005 database. This tool generated a detailed log of any errors or warnings with the Access data. After each import, we analysed the import log and collaborated with the Coaching Ireland to fix any data errors. This process of import/analyse/collaborate continued until all data was successfully ported.

The Web interface was built using ASP.Net and AJAX to provide a smooth user interface to the new data. Strict security controls were implemented in ASP.Net to ensure that users of the site can only see the data they are authorized to view. This allows Coaching Ireland to make their CoachingDB available from around the world, safe in the knowledge that the data is secured and restricted appropriately.