brahm neufeld

Availability Page

SCI-FI Registration System Overhaul

In the fall of 2008, I was given the green light to develop a completely new registration system for SCI-FI, scrapping the previous system and starting from scratch. While it's not traditionally good practice to disregard everything that came before you and start from scratch, SCI-FI needed it. We had been using a system that started as a comma-separated value imported into an access database, which evolved into a proper registration form when we started accepting VISA/MasterCard payments through Information Technology Services at the U of S.

SCI-FI's technical setup was functional, but not administrator-friendly or user-expandable. While the system worked, it was not a long-term registration solution, nor was it manageable by someone without significant training in Information Technology. Expandability and user-friendliness are key features of the new system.

The system needed to be specially developed because there are no industry solutions that meet the needs of a program like SCI-FI. In a nutshell, our required features include:

  • A workshop registration system.
  • A sensible, scalable, expandable camper registration system. For example, if we were to absorb another camp, or start a completely new program outside of the SCI-FI banner, it should integrate seamlessly.
  • Completely safe and secure data storage.
  • A staff portal to give staff access to the pieces of information they required - namely, schedules, camp totals, feedback, and overtime tracking.
  • A feedback and survey system that could take feedback from campers or teachers and deliver it directly to the staff members directly involved in the program delivery, through the staff portal.

The project consisted of three main parts: the online interface, the administrative (office) interface, and the backend database structure. The camper and workshop registration forms were written in PHP, the administrative interface was developed within Microsoft Access, and the backend data is stored in a MsSQL server in Engineering. During development, we approximately doubled the number of tables we're using (upwards of 20 from just under 10) and our utilization of views exploded! By carefully planning which tables were needed and how data would be linked together, we've eliminated a TON of redundant data and made scriping and querying about a thousand times easier to deal with.

The system was rolled out for the 2009 camp and workshop year. As of this writing, it is humming along nicely, and have finished or are just finishing putting the finishing touches on some of the best features:

  • Online Interface
    • Flexible extra options for parents - options for Extended Hours, programs like Girl Power, magazine subscriptions, and discounts can be made available on a per-camp (ie "Banting"), per-program (ie "Saturday Clubs"), per-date, or per-location (for example, some options are only available in Saskatoon).
    • Integration with Information Technology Services' latest Epayments system, so we can accept VISA/MasterCard payments.
    • Advanced availability features so parents know which camps have space remaining.
    • Ability for one contact person at a school to register up to 10 classrooms at once, without having to re-submit a form over and over.
  • Administrative (Office) Interface
    • One-click individual receipting (send to printer, or open a PDF).
    • One-click batch receipting (immediately print all outstanding receipts to be mailed, OR, immediately email all outstanding receipts to be emailed) - done via CONFIRMATRON 5000.
    • Better payment tracking - ability to submit multiple payments on a registration, or spread a payment across multiple registration. No more updating a static "amount paid" field.
    • Automated cash deposit worksheet - all cheques, credit card transactions and online payments are recorded in two payment tables. These tables are used to generate automated deposit sheets to give to Financial Services Division at the U of S, and a script imports transactions into QuickBooks. This compresses about an hour of work into approximately 45 seconds, not including the walk to the Admin Building.
    • Availability-checking, statistics about the number of times instructors have been paired together, and an overtime tracking system that is linked into our staff portal.
    • Clever archive and cancellation system - no need to ever "restart" a database or new table for a new year of camp.
    • Ability to control every camp or workshop program available through the online registration form - no hard-coding involved.

The project took about three months to complete. One of my favourite things about technology projects like this is that my knowledge always continues to grow; I always find really cool new features or possibilities. I would like to someday update this page and explain some of more-cool features of this new system in depth!

Last Modified March 4, 2009