NeatClubs.COM - Class Management Plan

This page outlines some steps involved in augmenting NeatClubs.COM to provide simple class management functionality. The goal is to deliver this functionality without "breaking" registration functionality that already works, and without making the changes intrusive to "non-club" customers using NeatClubs for other requirements.

•

Create the SQL schema for a new "participants" table. Unlike other the member and event level tables, this table will have field names hard-coded at the SQL level for simplicity. Stored fields names will correspond to those already documented in http://docs.neatworx.com/index.html?recommended_field_mapping.htm - we will leave half a dozen open fields for future expansion or for club level customization.

•

For many registrations and event types the participants table will never be used. (for example special event registrations, contact us forms, inquiry forms, sales events etc). We will adopt the convention that if the registration form includes at a minimum "FIRST_NAME", "LAST_NAME" and "BIRTHDATE" as named fields in the custom form associated with a registration activity, we will assume these need to be entered into the participants table. This will introduce the requirement that for recreational class registrations, camps and resource reservations (birthdays), these fields will need to be present in the event specific registration form. (they generally are anyway)

•

We will introduce a new system setup option called "Enable Class Management Features". If disabled, we will seek to make these changes invisible to existing customers. If enabled, the "Manage Members" menu will presented as "Account Holders", and a new menu option will appear allowing us to manage "Participants". Also, new features dealing with attendance will appear under appropriate management menus. (The management of participants from an interface standpoint will be nearly identical to the management of "Members" except that the data fields will be non-configurable. We will reduce learning curves by having the two interfaces look as similar as possible.

•

To speed database manipulation, we will "cache" first_name, last_name and birthdate fields to the event_detail record. (saving the need to load the event definitions and parse the custom field structure specific to each event to figure out what field in the event_detail record maps to values that will uniquely identify a participant) - we will additionally add to each registration "instance" other values we anticipate needing in future such as achievement level, instructor, notes and keywords. This will set the stage by allowing us to track progress by displaying the history of program participation for any participant.

•

If a registration with a name and birthdate combination is received that is not already in the participants table, a new row will be inserted and this will be considered a "new" participant. The Participant table will use it's own internal participant ID, but a generic "Member ID" will also be included that can optionally include something like a G.O. number.

•

If a new registration is received, and a participant record is already found to exist, the registration transaction will be assumed to be an "update" to the participants table. Depending on the available fields in the registration form (which are inherently variable), the system will do its best to "Map" new fields. In the case where a valid field name is found on the form, it will be assumed to over-ride previous data for the matching participants field. (for example, a new registration with the field named "HOMEPHONE" would be assumed to over-ride a previously stored value of "HOMEPHONE" - if the home phone had in fact changed, the earlier "instance" of the registration would still contain the old data - useful in case someone made a data entry error in a recent registration and the older data was in fact correct.

•

Participants may optionally be associated with an "account holder" (normally the parent of course), but sometimes the administrator. Accounts holders are what we now refer to as "Members" - generally people with login access to the system, although some "Members" (notably newsletter recipients) may not have access.

•	There must be an interface to re-assign a participant to a different account holder (in cases where we create an account holder after the fact and need to assign participants to the parent record)

•	If a "member" is deleted, participant records associated with the member / account holder should be reassigned back to the primary site administrator (with a user ID of "0") - thus making sure we don't invalidate our history by accidentally deleting members.

•	We will be able to optionally delete a participant, but the more common activity will be to make them inactive which will be similar in concept to archiving them. Archived members will still show up in historical reports - they just won't clutter our participant database.

•	From a participant record, we should have a "hotlink" back to the "account holder / member" record. Similarly, from a parent record, there should be a hot link to view any participants associated with that "account holder / member.

•

We will need to "end of session" functions to go through the participant record and make all members "inactive" at the start of a new year. For convenience it would be useful to be able to automatically activate or deactivate participant records with corresponding registrations in a particular date range.

•	The Output of registration data (in CSV format) should be made more granular so that it supports exporting files for a session and/or year and optional within specific dates ranges as well.

Phase 2: Establish "Non Registration" in-house Interface to add participants and registratrations

Currently, the administrator can create a new registration and optionally not pay. While this works, the problem with this approach is that the system interprets the administrator as being the account holder (parent). Ideally, when an administrator creates a new registration, we would like a different interface that allows them to enter information about a parent (thus creating a new account holder, possibly with no immediate login privileges) and then go on to provide class or competitive program registration.

Currently, the applicability of supplementary fees can be tied to the absence or presence of a record in an external (e-sport) database, or can be made user-selectable, but there is no way to determine fees based on the presence of records in our own database.

•	The event setup should be extended to make fees apply or not apply depending on whether there is a registration record within the last "X" months of the program start date that has a paid supplementary fee associated with it.

•

This will work across multiple sports and avoids the problem of tracking the specific dates of various governing bodies fiscal years. (For example, in the case of Gymnastics, a registration in late July can reasonably be assumed to pertain to the next fiscal year - club owners will be able to decide what is reasonably a fall vs. summer registration depending on how they structure their programs) - For the winter programs starting early January, clubs might determine that the fee applies if no registrations were received in the last 5.5 months as an example.

•

When viewing the received registration summary, we should enhance the view so that other sessions within a given year can be switched to quickly via "quick links" - would search the database, extract the session for which their exist class registrations in a given year - if the choices were "Fall, Winter and Spring" these would be displayed across the top of the screen as "quick links".

•	New registration summary view showing all programs by time slot with registration tallies in a single printable view (as above with the ability to navigate between sessions within a year)

•

This is quite difficult, but a feature that ensures that supplementary fees are charged only once per participant for a shopping cart style registration involving multiple registrations would be very useful. (this would need to be a special case and would need some assumptions to make it "do-able" - for example we would need to accept that the first supplementary fee would always be the governing bodies membership fee.

As a consequence of separating "account holders" from "participants", this will give us a change to better address a long standing problem - that being that a participant can in fact have multiple roles. We would leave the account holder member type definitions pretty much as they are. We should create a second group of member types that would apply to participants. We would want to allow participants to be associated with more than one role or member type. (an athlete competing in two disciplines would be a good example)

•	Attendance is basically a property of the "event" since each event ID is unique. Attendance could in theory apply to any event type, but will be most relevant for classes and camp style registration events.

•

Internal representation would be a "bitmap" by time slot stored in the event_detail record - we assume that after events are active for registration, administrators cannot change the order of the time slots since this would invalidate attendance (we make this difficult already and display a warning message) - rather they can only choose to close a time slot or add a new time slot to the end of the time slot list. (in which case attendance would be "null" for that time slot as required)

•

If there were 10 classes per session, the attendance would be stored as something like 1|1|0|1|1|1|1|1|0|1 for a registration instance depending on the number of class instances. (exclusion dates would be treated as "0"s). For future compatibility the design for attendance would need to support classes that occur more than once per week. The attendance record itself however would have to be at the "event_detail" record - they attendance "geometry" comes from the event, but applies to each registration instance made against the event.

•	We need an attendance data entry screen that would effect the attendance stored with each class (or camp) style event. When displaying any type of class list, or registration tally screen we should have a "quick link" to the attendance function

•

The CAMP attendance track is especially complicated since the "geometry" of the registration does not necessarily correspond to the "geometry" of the event definition. This is because participants will normally register for a subset of available camp days. We may want to simplify this and make attendance selectable for any day that camp runs to accommodate the idea that I child might attend a day that did not actually pay for and register for in advance.

•	We will require an attendance "update" view that shows participants for a class or program as "rows" and class dates (or camp dates) as columns. This will simply be an interface that affects the stored

•	We should elevate "Instructor" or "Coach" to a role with special meaning so that any account holder with this role can login, see a view of classes where they are the instructor in a given year and session (for "active events only") so that Coaches can enter the attendance via the web-site.

•	The printing of a formatted Attendance Reports should be just a variant of the Class List function below. (since we may want to control the fields that show on the attendance list along with a class list)

•

This is pretty simple - we need a report that based on the Year, Sessions, Program and time slot, generates a list of participants where the fields displayed on a per-participant basis would be user-configurable. Participants would include both those registered on-line as well as those manually entered into the class management system by an administrator.

•	We should also generate the same lists for all participants in a given program (regardless of time slot) as well participants for a given year or year / session combination.

•

The Attendance list would just be a variant of this class list. Printed attendance lists would reflect any attendance information in the system thus far. (instructors could maintain paper lists and update attendance at the end of a session, or they might choose to maintain attendance on-line every week for all their classes depending on their preference

We will establish three additional "transaction types" that can exist in the event_detail database. (right now we only have one transaction type - that being a registration) We will introduce a 'tx_type' field where the default type will be 'R' pertaining to a registration record. Additional types will be:

a) generate a "GL" by participant. We can search the event_detail table for matching participants and sum the amounts owing and the amount paid for each registration transaction. Amounts paid and amounts due will most often balance for each instance of a registration, but this need not be true.

•	Manual Adjustments can affect either the amount due or the amount paid with explanatory nodes (in the new payment_notes field). For example, a note indicating that a fee was forgiven, could be entered as a manual adjustment along with an explanation.

A G/L should also be able to be constructed "by account holder" or parent. We should be able to post a payment "by family" so that one payment transaction can apply to multiple amounts due.

•	Need to be able to allow parents with an e-mail address on file (who have login privilege enabled) to request a randomly generated username / password combination. (their e-mail address as their default username probably makes the most sense).