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/cacw6n4tvsmdd3gc3ux+3-ydgkw4mzehgcfnmup-vdwvmeao...@mail.gmail.com ** 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.
