SCANS is a non-profit society providing university-level courses to the elderly. [url removed, login to view]
Each course is presented by a retired university professor over 6, 8 or 10 weeks (a term). Up to 25 different courses are offered each term at ~12 different locations. There are currently 3 terms each year (fall, winter & spring), but SCANS is considering offering courses in the summer.
Members can join or enrol as a member in SCANS at any time during the year, but most join (or renew) in the four weeks leading up to the fall or winter terms. Membership expires after one year.
Only SCANS members can register for courses, although SCANS does allow new members, whose payments have not yet been received to reserve space on a course (payment must be received by the first class).
Unlike colleges, SCANS do not pay for each course they take. Being a membership-based non-profit organization means they do not have to charge tax, since taking an unlimited number of courses is a benefit for all members.
SCANS requires a database to manage the Membership and Course Registration processes:-
1. potential/new members will be able to join SCANS and their details will be stored in the membership database (see attached table);
2. existing members will automatically receive two email reminders to renew 6 and 3 weeks before their membership expires;
3. both new & existing members will be able to pay their membership dues (currently $120/yr) via PayPal (leading to 'full member' status). If they are uncomfortable or unable to pay by PayPal, they will be a 'provisional member' until SCANS receives a cheque and the SCANS Administrator (Registrar or 'Reg') changes their status to 'full member', but they will still be able to register for a course;
4. new joiners will immediately receive a unique 5-figure membership # which will also include their expiry date (eg 00001-0412, for April 2012). Members who renew will just receive a new expiry date added on to their
existing membership #. Both parts of this # will be required when a member registers for courses [I'm open to suggestions here - this is the best way I can think to organize];
5. SCANS Admin will be able to list all the details about each course (see attached Table).
6. SCANS Admin will be able to choose when a term's courses become visible on the website, and when they are to be no longer visible. As well, they will be able to choose when a course's registration opens, and when it closes (for example, courses will be visible on the website between Dec 3 & Mar 31; a course begins Jan 15; that course's registration window opens Jan 2 and closes Jan 23.). Different courses can have different registration windows, but we should have the ability to "repeat previous" since some courses will have the same dates;
7. after the application has been received (whether paid or not), members are able to register for as many courses as they wish provided the registration is open and the class is not over-subscribed. When a course is full, a FULL flag should appear. However members may still apply, but they must be told they are going on a waiting list. A FULL course, and the number of people on a waiting list should be clearly visible to members viewing the course details (to discourage people from adding their name to an over-subscribed course);
8. timetable conflicts will be denied (eg. a mbr cannot register for two Tuesday afternoon courses);
9. although mbrs can join or renew at any time during the year, there will only be 3 expiry dates in each year: Jan 31, Apr 30 & Sep 30 (for example, members who join between Feb 1 & Apr 29 will all have the same Apr
30 expiry date, but mbrs who join after May 1 & before Sep 29 will have a Sep 30 expiry date);
10. Admin should be able to run all sorts of reports. Such reports need to be customizable, editable and saveable.
11. Installl/test the db on a Unix server.
Lastly, I'm not a database designer, so please forgive the poor description & attachments. Do ask Qs