I am designing the scheme of several tables (user and Courses / Seminars / talks) in which a user can have multiple courses. These courses are in a Courses list; this part is already related; but now I see the need to design a table of prerequisites.
The problem that this poses:
- It is that some courses do not have prerequisites and that those that have prerequisites are courses that are in the same list of courses / certifications.
this is the layout of the schematic:
The file: Link
The prerequisite to a course is a course. So, you have a table of courses, with course numbers and descriptions. A course may have many prerequisites; in technical terms that means that course : prerequisite has 0:N cardinality. Because any M:N relationship requires a table, you need a table. Define a prerequisites table with two columns that together constitute a key: the course number and the prerequisite-course number.
Suppose you have course C, but to take C you must first have taken courses A and B. The courses table has 3 rows, one for each course, A, B, and C. The prerequisites table has two rows:
Course Prerequisite
C A
C B
Both columns have foreign keys to the courses table, because every course in the prerequisite table is a course, and every prerequisite is a course.
How do I pose / relate / make this two-way relationship in Mysql Workbench?