How can I make a bidirectional relationship between tables?

Question

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:

1. 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?

Answer 1

Seems to me like the PREREQUISITES table materializes the N-M relationship between courses. I have no idea how MySQL Workbench works (or even what it is), but in plain SQL, it would be something like this ...

Assuming a COURSES table like this:

  create table courses (
    course_id int primary key,
    course_name char(50)
  );

The PREREQUISITES table would look like this:

  create table prerequisites (
    course_id int references courses,
    prereq_course_id int references courses,
    primary key (course_id, prereq_course_id)
  );