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
- 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
- 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
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