Linking to the correct table in a Foreign Relationship
Suppose I have these tables: User(roles=admin,teacher, student), StudentProfile, TeacherProfile, Course, Enrollment, Scholarship etc. The User table has auth stuff like first_name, last_name, password, email etc. (using Django framework). You can already guess this is an LMS platform.
A student user can enroll for a course. Now should the Enrollment link the StudentProfile table or the User table? I was vibe coding with Claude free, and I now realized that it connected everything to the User table. Although to me logically it seems a student can enroll for a course, so the enrollment should be linked to student profile, right? the argument presented by Claude is that it is easier to lookup without requiring complex joins.
I initially thought that Enrollment should be linked to StudentProfile since logically a student enrolls in a course. However, after reconsidering the design, it makes more sense to associate Enrollment with the User model.
The main reason is that User represents the core identity and is directly tied to authentication and actions in the system. Enrollment is essentially an action performed by a user, so linking it to User keeps the design simpler and more flexible.
This approach also avoids unnecessary joins when querying (e.g., request.user.enrollments.all()), and it allows for easier scalability in case roles evolve (like a user being both a teacher and a student).
The StudentProfile should remain focused on storing additional, role-specific data rather than being responsible for core relationships like enrollments.
So overall, linking Enrollment to User is cleaner, more maintainable, and aligns better with standard Django practices.