Introduction

The National Coaching & Training Centre (NCTC) is responsible for educating and supporting coaches. It also provides 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.

The NCTC is responsible for maintaining an accurate database of more than 30,000 sports people which is used to send out regular correspondences, run professional development courses and answer queries from sports people on their qualifications.

Problem: A 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 NCTC staff on their internal network. NCTC wanted to upgrade this database to a more reliable platform and make it available via a Web interface.

Challenge: Data Migration

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 golive date.

Solution

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 validate all data from the Access database and import it into the SQL 2005 database. A detailed log was generated by the tool which was analysed for any data errors and Action Point then collaborated with NCTC to fix any issues. 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 data management interface with advanced reporting functionality that was comprehensive yet intuitive.
Strict security controls were implemented in ASP.Net to ensure that users can only access the data which they are authorized to view. This allows NCTC to make their new Coach Management Database accessible throughout the world, 24/7.

Technologies Used

ASP.Net, AJAX, C#, SQL 2005, Visio, MS ReportViewer

Download a PDF version of this case study