0

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:

enter image description here

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?

1 Answer 1

2

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)
  );
7
  • Thank you, I comment that the foreign key can be null since there are some courses that are basic and do not have prerequisites.
    – user237107
    Commented Dec 13, 2021 at 14:27
  • the relation is 1 to N but with the null option checked ... in MySQL Workbench
    – user237107
    Commented Dec 13, 2021 at 14:27
  • well, at the moment I am not creating tables I am only creating the schema modeling.
    – user237107
    Commented Dec 13, 2021 at 14:28
  • If a course does not have any dependencies and does not have any dependents, then it does not appear in the PREREQUISITES table at all. Only actual relationships appear, i.e. the foreign keys in that table will never be null. So the table above should include explicit NOT NULL constraints to reflect that. Commented Dec 14, 2021 at 13:18
  • you're right; So the scheme of the table is how you raise it?
    – user237107
    Commented Dec 14, 2021 at 14:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.