Thanks for your input Ted. What I think I will do, on day-1 of a session I will use the spot table to generate a 1-session record per spot/student in that class for that session... I will treat it as a billed transaction so classID/studentID/familyID/chargedAmount/session or date/ and a 5 to 11 slots (tinyInt or boolean, .t.=present, .f.=absent or use the numbers -1,0,+1 for absent/made up/present)... and maybe a small text slot for a comment. When a student moves to a different class or starts mid session, you would adjust the amount charged with explanation in memo.
We have some clients that will pay just to keep a spot in a class open when not attending because we have waiting lists to get in. Most sessions are 4 to 5 weeks but summer is an 11-week session where they may opt to pay for 5 / 7 / 9 / 11 of the weeks so families can take vacations. Isn't this just an invoice item... like 1-leotard, 1-warm-upo, 1-session then by your suggestion, maybe I could figure out a drop down to choose which client gets billed per item.... I think I'm getting it clear in my head.... now could I put this on a cloud and then take attendance with an ipad or cell phone? Gary -----Original Message----- From: Ted Roche [mailto:[email protected]] Sent: Monday, April 22, 2013 1:16 PM To: [email protected] Subject: Re: normalization On Mon, Apr 22, 2013 at 11:54 AM, Gary Jeurink <[email protected]>wrote: > Every time I think I understand something a new wrinkle throws me for a > loop. I have a client table (family to bill). I have a student table > (families have multiple kids) and a classes table (class with the students > enrolled). I made an intermediate table spots (classID, studentID, > clientID). This was easy enough until I was asked to provide attendance for > each class per session. Some sessions are 5-weeks, team classes are billed > monthly. Now I'm stumped because students can start mid-session, change > classes, and even make-up a set number of missed classes. > > > > Maybe I'm just being lazy but with peak enrollment at 450+, I'm feeling > overwhelmed trying to say (present or absent. or make-up per student). Is > the attendance table going to be like the spot table except it will also > contain a session/date/week. It sort of seems redundant in a way. > > Interesting questions. If a student always has the same family paying for their course, the Student table should have a FamilyID field, a foreign key that says there is a one Family-to-many-Students relationship. This might not be the case, however, if two parents split, mix with other families, etc. Then you might have bio-parents, legal parents, and it gets to be pretty complex. But in the simple case, move the Family-to-Student relationship out of the Student-to-Class relationship. Next, you have Students signing up for classes, and you have two problems: in advance, assign no more than the maximum number of students to a class. Then, after the fact, record which students actually attended which classes. So, the "Spots" table (which ought to be StudentID and ClassID only) is a representation of who's in which class, today. There's no history, no date ranges. And Attendance, the new table, is Student, Class and Date/Time. Attendance is kind of a "write-only" table, where you're not going to go back in and revise (usually, except for data entry errors), whereas Spots is a here-and-now table of who's assigned to which classes now, which can have adds, edits and most importantly, deletes. (As a side note, I hardly ever actually delete, just mark as inactive and retain as history, archiving off as needed. But that's a finesse point.) That would be my guess, based on what you've said. What have I got wrong? -- Ted Roche Ted Roche & Associates, LLC http://www.tedroche.c <http://www.tedroche.com> --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/0AD1907467514810886D3F74B71467B1@OwnerPC ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
