Database design for managing course prerequisites

Courses
1

I’m working on a school system and need help with the database structure. I want to store info about students, courses, and course requirements. I’m not sure how to set it up so I can easily check if a student can take a certain class.

Here’s what I’m dealing with:

  • Some courses have no prerequisites
  • Others need one or more specific courses completed first
  • A few advanced classes require a certain number of courses from a group

For example:

Biology 101: No prereqs
Chemistry 201: Needs Chem 101
Physics 301: Requires any 2 from Physics 201, 202, or 203

How can I design the database to handle these different scenarios? And what’s the best way to query it to check if a student qualifies for a course?

I’ve tried a few approaches but they all seem messy or inefficient. Any tips or examples would be great. Thanks!

2

Hey Mia_79Dance! Your database design question got me thinking. :thinking:

Have you considered using a graph database for this? It could be a really cool solution! The courses would be nodes, and prerequisites would be edges connecting them. That way, you could easily traverse the graph to check if a student meets all the requirements.

But if you’re sticking with a relational database, what about a flexible approach using a JSON field? You could store the prerequisite logic as a JSON object in the courses table. Something like:

{
  "type": "any",
  "count": 2,
  "courses": ["Physics 201", "Physics 202", "Physics 203"]
}

This way, you can handle all your scenarios in one field. Then you’d just need to write a function to interpret the JSON and check against the student’s completed courses.

What do you think about these ideas? Have you tried anything similar? I’m really curious to hear more about your project!

3

hey mia, ive worked on similar stuff before. what about using a junction table for prerequisites? you could have columns like course_id, prereq_id, and maybe a type field for different scenarios. then u can join it with the courses table to check eligibility. its pretty flexible and easy to query. just a thought!

4

I’ve dealt with a similar challenge when building a course management system for a local community college. Here’s an approach that worked well for us:

Create separate tables for Courses, Prerequisites, and StudentCompletedCourses. In the Prerequisites table, include a course_id, prereq_course_id, and a type field (to indicate single course, one of many, etc.). For the ‘n out of m’ scenario, you can introduce a group_id to link related prerequisites.

For querying, a stored procedure that checks a student’s completed courses against the prerequisites worked effectively. It handles the different types with conditional logic, keeping the process efficient and flexible.

One tip: consider adding a ‘minimum grade’ field to prerequisites. Some schools require not just course completion but a certain grade, which prevented us from undergoing a major refactor later on.

Hope this helps point you in the right direction!