Upgrades to SpeedCouncil.org - The Database

Speed School Student Council has consumed more of my time than I should probably admit. Most of my time has been dedicated to a major overhaul of council's website: http://speedcouncil.org Therefore, I feel it is appropriate to discuss all of what I've been working on.

My efforts have been focused on bringing a rich array of content to the website that focuses on the members. The original problem that I was attempting to solve was implementing an accurate way to track member attendance. The old way of tracking attendance was using an Excel document, but I moved to Google Docs to allow for collaboration. However, this solution still was limited by the constraints. A normalized database structure was needed to accurately track member attendance. At the same time it would be possible to make membership and attendance records easily available online.

The entire system is based around members. Each member has a position and a default status. Their position along with their status determine their voting rights along with some other important information.

Attendance records are organized by semesters then by actual meetings. Each meeting has an associated type which in turn has specified positions that are required to attend each type of meeting. In order to track attendance records which tie a meeting, a member, a members current position, and their attendance status together. In this way, as a member changes positions during their course of involvement on the council, the position they held during a specific meeting is still tracked. In turn, the quorum for each meeting is tracked independently and can vary based on when members are elected mid-semester or even if a student on co-op shows up to a meeting.

Committees are another aspect of the council that can now be managed. Committees are easier to manage than attendance because they do not require historical data, rather they are a snapshot of the current status. Committees have a chairperson and then members. Members are tracked by tying together a distinct committee with a distinct member resulting in a record which represents committee involvement.

To help promote involvement, achievements were also added to the website. Taking a page out of the gaming world, achievements promote council involvement by tracking and displaying the accomplishments of members. Achievements are broken down into categories and have associated titles, descriptions, and images. In addition, achievements can be progressive requiring a number of tasks to be completed to earn the achievement. The earned achievements tie together distinct achievements with members, but also track the progress of the achievement.

At this point in time I have not open sourced all of my code. It would be something I would like to do, but at this point in time too much of the application is hard coded for the requirements of SSSC. Making it generic would dramatically increase the complexity, so this will likely not be done in the short term.

That covers the basic of the database without going into the specific structure. There is quite a bit more information that I can share about all of the work I have done, but for now the easiest way to get an idea of what I've done is to simply visit SSSC's website and look at the members, committees, and achievements.