PaulB-TheOneAndOnly (1/14/2012)
patrickmcginnis59 (1/13/2012)
Long story short, I used an array of characters and a nice scrolly matrix gadget with dates across the top and students down the side, the whole matrix being a course section, and I allowed a few extra attendance types.If the above is the description of your "course" table I can tell this is not normalized at all.
It would be easier for us to understand your scenario if a list of involved tables is provided including a summary of the data each one contains alongside PKs and FK relationships.
Well if it had been normalized, and I wanted to track each attendence day, I would have
COURSE_ID VARCHAR(20),
STUDENT_ID VARCHAR(10),
MEETING_ID DATETIME,
ATTENDANCE VARCHAR(1)
With everything but ATTENDANCE being a foreign key. As I actually did it, I had:
COURSE_ID VARCHAR(20),
STUDENT_ID VARCHAR(20),
ATTENDANCE VARCHAR(1) OCCURS MANY TIMES
and
COURSE_ID VARCHAR(20),
MEETING_ID, DATETIME OCCURS MANY TIMES.
and more importantly, the attendance and meeting_id occupy corresponding positions in their respective rows. I did it that way because everything worked nicer when it was "prejoined" so to speak.
Now this is according to my rather informal understanding of normalization. I could elaborate further if I took the time and read up, and of course I'm the new RDBMS person here, feel free to poke holes in anything I post. Its also worth noting that a valid optimization is to only store the absences or other abnormal stuff we want track and otherwise assume the student was present for that meeting.