What a college management system taught me about data modeling

A college management system is the kind of project that looks like a beginner exercise on the surface. Students, teachers, courses, grades, attendance - just a handful of tables and some forms, right? That is exactly the trap. The code is easy; the data model is where every real decision hides, and getting it wrong there means pain everywhere else.

The first thing I got wrong was treating enrollment as a plain many-to-many between students and courses. A bare ManyToManyField answers "who is in this course," and nothing else. But an enrollment carries data: a grade, a status, the date the student joined. The moment you need any of that, the implicit join table is a dead end.

class Enrollment(models.Model):
    student = models.ForeignKey(Student, on_delete=models.CASCADE)
    course = models.ForeignKey(Course, on_delete=models.CASCADE)
    grade = models.CharField(max_length=2, blank=True)
    status = models.CharField(max_length=20, default="active")
    enrolled_on = models.DateField(auto_now_add=True)

    class Meta:
        unique_together = ("student", "course")

The rule I took away: the moment a relationship has attributes of its own, model the join explicitly. Django even lets you keep the friendly API with ManyToManyField(through="Enrollment"), so you get readable access and a real table you can hang fields on.

Attendance taught me the same lesson from a different angle, plus a performance one. One row per student per class meeting is a lot of rows, and the query you actually run - "attendance for this course this month" - has to stay fast. That means an index on the columns you filter by, decided up front, not after it is slow.

class Attendance(models.Model):
    enrollment = models.ForeignKey(Enrollment, on_delete=models.CASCADE)
    date = models.DateField()
    present = models.BooleanField(default=False)

    class Meta:
        indexes = [models.Index(fields=["enrollment", "date"])]
        unique_together = ("enrollment", "date")

Notice attendance points at the enrollment, not at the student and course separately. That was deliberate: an attendance record only makes sense for someone actually enrolled, and hanging it off the enrollment makes the alternative impossible to express. The schema enforces the rule so the application code does not have to remember to.

Roles were the last piece. Students, teachers, and admins are all users, but they see different slices of the same data. I kept one user model with a role rather than three parallel tables, and scoped what each role can touch at the query level - a teacher's queryset is filtered to their own courses before any view logic runs.

None of this is advanced. But it is the difference between a schema that absorbs new requirements and one that fights you on every feature. The code in a system like this is mostly forms and lists; the thinking is almost entirely in the shape of the tables.