Mysql how to avoid repeating myself

I have a table students with the foll开发者_JAVA技巧owing fields.

Id,FirstName,SecondName,Photo,Student_ID

I also have a table called class_of_2011 with the fields

Id,FirstName,SecondName,Photo,Student_ID,Subjects

I want to select some specific students from table students and have them in table class_of_2011,but I already have the names in table students.I am thinking the only way to do this is to copy the names i want to the table class_of_2011,but since there will be a class of 2012 and beyond,I feel like I will be simply copying data from one table to the other.

Is repeating myself inevitable in my case?

It looks like this could be normalized easily. Why not have your class_of_ tables simply have a foreign key to the student table's id column?

StudentId,Subjects

In this way, one student record could be associated with several classes, in case someone is on the 5-year plan.

I'm assuming that the Student_ID field in the Students table is their id number or something, and not the primary key of that table.

Students Table Id,FirstName,SecondName,Photo,Student_ID

Subjects Table Id,Subject

Student_Subjects Table Id,Student_Id,Subject_Id,Year

You may then assign a student multiple subjects, for multiple years.

The class_of_2011 table should contain the primary key of the students table and none of the other "repeated" data. All of the other columns you're interested in can then be obtained by joining the two columns together in a query.

I would restructure the data if possible... Something like....

Student Table
ID, Name, Address, other common specific to student

GraduatingClass Table
YearGraduate, StudentID

Enrollment Table
StudentID, ClassID, SemesterID