How to create Student Subject with prerequisite Database

Question

i am trying to create a database for subject prerequisite this is my current table.

"student"

-----------------------
|studentID | FullName |
-----------------------
|1234      | John    |
|1235      | Michael |
|1236      | Bryce   |

"subject_bsit"

-----------------------------------------
|subject_id| subject_name  |  pre_id    |
-----------------------------------------
|    1     | Programming 1 |    NULL     |
|    2     | Networking    |    NULL     |
|    3     | Algorithm     |    NULL     |
|    4     | Physical Educ |    NULL     |
|    5     | Programming 2 |     1       |

This is the Junction table to connect the two now.

"student_subject"

------------------------------------------------
| student_id | subject_id | Grade   | Enrolled |
------------------------------------------------
|   1235     |      1     |    0    |     0    |
|   1235     |      2     |    0    |     0    |
|   1235     |      3     |    0    |     0    |
|   1234     |      1     |    0    |     0    |

Example if studentID 1235 still doesn't take the subject_id 1 which is programming 1 the user cannot add the subject_id 5 that is programming 2 because it is the prerequisite of programming 1.

i don't know if my table is correct. If it is correct how can i design that database i don't know how can i connect the prerequisite to subject and and the student id thank you so much.

Your data structure is fine. You just need right JOINs based on the exact task at hand. — PM 77-1, Mar 22 '19 at 21:06
Your data structure looks okay. The only thing you need to do is before add record to student_subject table, you need to check whether an entry exists for the prerequisite subject(if applicable) — Rohit Shetty, Mar 22 '19 at 21:08
@rohitshetty what happens if user b inserts that row *while* user a is checking — Strawberry, Mar 22 '19 at 23:17

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

First you need to find all the prerequisites (parents) of the specified subject recursively with the following code (MySQL 8+):

set @newSubjectToAdd = 5;

with recursive cte (subject_id,subject_name,pre_id) as
(
  Select subject_id,subject_name,pre_id
  from subject_bsit
  where subject_id = @newSubjectToAdd 
  union all

  Select sb.subject_id,sb.subject_name,sb.pre_id
  from subject_bsit sb
  inner join cte 
  on  sb.subject_id = cte.pre_id
)

Select pre_id from cte
where pre_id is not null
order by subject_id

(Demo1)

Then by checking if the student has all the subjects obtained from the previous query you can decide whether to permit the user to assign the subject to the student or not.

Update:

If the following query does not exist then you can add the subject to the student. You can do it either by if exists or by a tricky conditional insert:

Select pre_id from cte
where pre_id is not null
and pre_id not in 
(select subject_id from student_subject
where student_id = 1235
and enrolled = 1)

Demo2

Hi i update my database in my post. If the user tried to enroll the subjectid=5 programming 2 and the subjectid=1 programming 1 is still not enroll . But if the enrolled = 1 on subjectid=1 then the user can enroll the subject programming 2. Do i need to use a if statement to get the statment that i want thank you so much. I don't know if my question is confusing. — Kimberypalet, Mar 23 '19 at 03:33

How to create Student Subject with prerequisite Database

1 Answers1

Update: