SQL Database : table column has relationship to itself

Question

Good day mate, I have a question here. I am trying to make a simple enrollment system, so I came up to a "subjects" table. The problem is this subject table has a prerequisite column that the value is another subject from the same table.

E.g
| subID    | title         | prerequisite   |
|    345   | math54        | 555            |
|    555   | physi43       | null           |

My question is I am doing it right? In my perspective, it's not because I can't make a foreign key at all. And to make it worst, one subject I have multiple prerequisite value

Answer 1

You are trying to map a 1:n relationship. In general, you don't do this with a single column. You would express this using a Prerequisites table:

create table prerequisites (
    prerequisites_id int, -- auto-incremented, serial, identity
    subject_id int references subjects(subject_id),
    depends_on_subject_id int references subjects(subject_id)
);

Because courses can change over time, prerequisites might also. That, in turn, suggests a type-2 table, meaning that there is an effective and end date for the dependency. However, that might be complicated for the work you are doing now.