I am overviewing a structure where a mapping table of student enrollments is done. I am wondering whether an additional column (class_category_id) to the mapping table would become a problem or a noticeable advantage. That additional key would be used to filter very often.
Here's a simplified structure of the database:
Class categories
id name
1 Math
2 Science
Classes
id name class_category_id
1 M101 1
2 M102 1
3 B101 2
4 P101 2
Student enrollments
id student_id class_id *class_category_id*
1 1001 1 1
2 1002 1 1
3 1003 3 2
4 1004 4 2
Common queries would include filtering the enrollments by the class category, without the actual need to get the class information itself.
It is not 100% clear whether class_category_id could have any advantages and disadvantages.
An important note would be that a category for a class will never change so updating multiple tables to update that would never be needed.
EDIT: Small note, this real table structure would be equivalent to this but with many more columns (for the non-mapping tables) and not really in any way related to classes/students.
select id, student_id from studentenrollment where class_id in (select id from class where class_category_id = 2)
as a filtering method?