https://ibb.co/Fb5nrf1 #image of ERD
I'm a science teacher, and I would like to record data for STUDENTs as they complete EXPERIMENTs in the lab. Each EXPERIMENT is looking at multiple SKILLs, and STUDENTs are scored on those SKILLs in many different EXPERIMENTs in many different LESSONs.
So here is the relationship between STUDENT, GROUP, TEACHER and LESSON:
a STUDENT can only be in 1 GROUP
but a GROUP can contain many STUDENTs
a TEACHER can teach many GROUPs
and a GROUP can have many TEACHERs
a STUDENT can attend many LESSONs
and a LESSON can contain many STUDENTs
a TEACHER can teach many LESSONs
but a LESSON can only have 1 TEACHER
Now for the relationship between LESSON, EXPERIMENT, and SKILL:
a LESSON can only contain 1 EXPERIMENT
but an EXPERIMENT can appear in many LESSONs
an EXPERIMENT can have many SKILLs
and SKILLs can appear in many LESSONs
My ultimate goal is to store information about how STUDENTs, GROUPs and TEACHERs are linked.
Then via LESSONs I'd like to store information about the EXPERIMENTs completed by STUDENTs (and obviously who the TEACHER was) and which SKILLs were assessed, along with a score for each SKILL assessed in the LESSON.
Lastly, I'd like to produce reports that show which STUDENTs have done which EXPERIMENTs and how their SKILLs are looking.