I am making a database of students in one school.Here is what I have so far:
If you don't like reading jump to the "In short" part
The problem is that I'm not happy with this design. I want the combination of grade, subgrade and id_class to be unique and to serve as a primary key for the students table.
I can remove the student_id and make a composite key from the 3 but I don't want that either. Maybe I should make another table lets say combination_id where grade, subgrade and id_class are foreign keys and there is one extra column comb_id that serves as ID for the table. And all the columns will be Primary Keys. But the problem is that those 3 columns can still repeat because of that extra column (comb_id). For example I can have the same grade, subgrade and class_id but different comb_id which will make the row valid because of the composite key of the 4 columns of the table (combination_id).
In short I want students_id to remain the only primary key of the table but to be a foreign key to another table which is somehow unique combination of grades, subgrade and class_id.
If I was not clear enough ask in the comments below and thank you in advance.
PS I'm sorry for the indescriptive title but I'm bad at naming
EDIT 1:
To be more clear:
grade can be 1 to 12
subgrade can be a to j
id_class can be 1 to 30 and it is your number in class
So a student can be from 7b class and his number in class - 5