Hi everyone! I hope everyone is doing fine. My name is Julian, and today we’re going to learn about Many-to-Many relationships in databases using an example of Students and Courses.
In real life, a student can enroll in multiple courses, and a course can have multiple students. This is an example of a Many-to-Many association.
We implement this using a junction table that connects both entities.
Conceptual Explanation
- We have two entities: Students and Courses.
- A student can enroll in multiple courses.
- A course can have multiple students.
- We need an intermediate table called StudentCourses to manage these associations.
Why Do We Need a Junction Table?
A junction table (also known as a bridge table or association table) is needed because databases do not support direct many-to-many relationships. Instead, we break it down into two one-to-many relationships.
In our case:
- A StudentCourses table holds references to both Student ID and Course ID.
- This allows us to track which students are enrolled in which courses.
- Without it, we wouldn’t be able to efficiently store and retrieve the relationships.
Implementation using Node.js, Express, and Sequelize
Step 1: Install Dependencies
Make sure you have Node.js installed, then set up a project:
npm init -y
npm install express sequelize mysql2
Step 2: Setup Sequelize and Define Models
const { Sequelize, DataTypes } = require(“sequelize”);
const sequelize = new Sequelize(“schoolDB”, “root”, “password”, {
host: “localhost”,
dialect: “mysql”
});
const Student = sequelize.define(“Student”, {
id: { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true },
name: { type: DataTypes.STRING, allowNull: false }
});
const Course = sequelize.define(“Course”, {
id: { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true },
title: { type: DataTypes.STRING, allowNull: false }
});
const StudentCourses = sequelize.define(“StudentCourses”, {});
// Many-to-Many Association
Student.belongsToMany(Course, { through: StudentCourses });
Course.belongsToMany(Student, { through: StudentCourses });
sequelize.sync({ force: true }).then(() => {
console.log(“Database & tables created!”);
});
Step 3: Creating a Controller for Managing Insertions with Try-Catch
const express = require(“express”);
const app = express();
app.use(express.json());
app.post(“/students”, async (req, res) => {
try {
const student = await Student.create(req.body);
res.json(student);
} catch (error) {
res.status(500).json({ message: “Error creating student”, error: error.message });
}
});
app.post(“/courses”, async (req, res) => {
try {
const course = await Course.create(req.body);
res.json(course);
} catch (error) {
res.status(500).json({ message: “Error creating course”, error: error.message });
}
});
app.post(“/enroll”, async (req, res) => {
try {
const { studentId, courseIds } = req.body; // Accept multiple course IDs
const student = await Student.findByPk(studentId, { include: Course });
if (!student) {
return res.status(404).json({ message: “Student not found” });
}
const courses = await Course.findAll({ where: { id: courseIds } });
if (courses.length !== courseIds.length) {
return res.status(404).json({ message: “One or more courses not found” });
}
await student.addCourses(courses); // Add multiple courses at once
const updatedStudent = await Student.findByPk(studentId, { include: Course });
res.json({ message: “Student enrolled successfully in multiple courses”, data: updatedStudent });
} catch (error) {
res.status(500).json({ message: “Error enrolling student”, error: error.message });
}
});
// Retrieve all enrolled courses for a student
app.get(“/students/:id/courses”, async (req, res) => {
try {
const student = await Student.findByPk(req.params.id, { include: Course });
if (!student) {
return res.status(404).json({ message: “Student not found” });
}
res.json(student);
} catch (error) {
res.status(500).json({ message: “Error retrieving courses”, error: error.message });
}
});
// Retrieve all students enrolled in a course
app.get(“/courses/:id/students”, async (req, res) => {
try {
const course = await Course.findByPk(req.params.id, { include: Student });
if (!course) {
return res.status(404).json({ message: “Course not found” });
}
res.json(course);
} catch (error) {
res.status(500).json({ message: “Error retrieving students”, error: error.message });
}
});
app.listen(3000, () => console.log(“Server running on port 3000”));
Now, you can insert students, courses, enroll students, and retrieve enrolled data via API requests using tools like Postman! 🚀
Understanding findAll() and include
What Will findAll() Return?
For Course.findAll()
[
{ “id”: 1, “title”: “Math” },
{ “id”: 2, “title”: “Science” }
- ]
- Returns all courses from the Courses table.
For Student.findAll({ include: Course })
[
{
“id”: 1,
“name”: “John Doe”,
“Courses”: [
{ “id”: 1, “title”: “Math” },
{ “id”: 2, “title”: “Science” }
]
}
- ]
- Returns all students with their enrolled courses.
What Does include Do?
- include: Course joins the Courses table to the Students table.
- This fetches the related courses a student is enrolled in.
Conclusion
Today, we explored the Many-to-Many association using Students and Courses as an example. We created:
- A junction table (StudentCourses) to handle associations.
- Implemented Sequelize models and relationships.
- Inserted and retrieved data.
- Created an API controller with try-catch for error handling.
- Understood addCourse, addCourses, removeCourse, setCourses, include, and findAll().
That’s it for today’s session! If you have any questions, feel free to ask. Happy coding!