Hi I'm trying to store multiple dimensions of information for a student using a mysql database.
Currently, each student has a profile with their name, age etc. and a user_id that identifies them. In addition to this, I would like to keep track of the classes they've taken each semester for each year of their attendance.
I've determined that the classes can be organized in their own three column table organized as: |class_id|subject|level|
For example:
|2,031|accounting|300|
|2,032|accounting|301|
I believe I can achieve my goal by creating a semesters table with multiple columns for the class_ids and a final column for the unique mem_id.. but I'm not sure how to organize the years without being redundant (following normalization rules) since each semester is either called Fall or Spring.
Ultimately, I'd like to check whether a student has values under a certain year, then if there are values under a certain semester and finally echo the classes taken for that semester on their profile.
I appreciate all the help I can get, thanks everyone.
You could create a student_class table which has student_id, class_id, semester, and year.
EDIT: Also, research data normalization in mysql for more information.