I work at a school and Im making a database of our courses where I will keep statistics of our courses. A course occasion is a single occasion of a course a certain semester. A course can only have one occasion per semester. We have two semesters per year, spring semester and autumn semester. The database currently has three tables,
3. Course occasions
The table Course occasions serves as a jointable where a course occasion is a combination of a course and a semester (for example, the course "ECO14 Economics" in “Spring 2018” is a course occasion. "ECO14 Econimics" in “Autumn 2018” is another course occasion). The table for Course occasions is organised so that a record among other consists of id (parent key), id_course (foreign key) and id_semester (foreign key).
Since a course only goes once per semester, I would like to do so that it is not possible to create a course occasion for a course during a semester where there is already a course occasion for that course.
What are the ways to solve this? I have thought about a related value list that filters out the courses that already have a course occasion during a specific semester but I do not know how to do it when there are two different related tables (1. Courses and 2. Semesters).
English is not my first language but I hope you understand. Let me know if something is not clear.