please can you check table structure/concept (1 Viewer)

[yabai]

hi,

i need a db to monitor attendance (I know there are examples that do this but I need to satisfy my own curiosity and haven't seen one exactly like i need). My plan is to set-up the tables as below. I now have one-to-many relationships. There are many students. each student will study many courses.

I would like a form that shows the student info and a subform that shows all the courses they are on and weeks 1 -10. i could then use the yes/no attribute to record an abscence. later i would then need a query to see which students had missed two weeks in a row or more than 3 in total.

I think i need a form bound to tblStudents with a subform control that is displaying a form that is bound to tblStudentCourse. i am then a bit stuck as i need another form that will display the course records for each student. am i off track??? any ideas would be great. thanks.

tblStudent
StudentID (PK, number)
FirstName
LastName

tblCourseID (PK, number)
Week1 (yes/no)
Week2 (yes/no)
etc.

tblStudentCourse
StudentCourseID (PK, autonumber)
StudentID
CourseID

 

[Mile-O]

tblStudent
StudentID (PK, number)
FirstName
LastName

tblCourseID (PK, number)
Week1 (yes/no)
Week2 (yes/no)
etc.

tblStudentCourse
StudentCourseID (PK, autonumber)
StudentID
CourseID

Well, tblStudent is fine.


tblCourseID is bad, since you are using a repeating group in it, Week1, Week2, etc. - bad, bad, bad!

Why do you need to bother about the weeks in a course?

As for the tblStudentCourse, the primary key should be a combination of StudentID and CourseId (a composite primary key) and not a new ID

 

[yabai]

hi,

thanks for taking a look. i think i need to have the weeks in there in some way as i need to query if someone is away in consecutive weeks. i did think they were repeating groups but wasn't sure of an alternative. should i create a table for each week?

cheers
mark

 

[ScottGem]

First I disagree with using a composite PK. You would want a unique index on the combination of Course and Student IDs, but I would use an autonumber as the PK.

Second, I agree that the CourseID table is bad. I do NOT recommend taking attendance this way. Instead what you do is record ABSENCES not attendance. You do not need a record for each class session. You just need to know if they weren't there.