CODEWITHSUNDEEP
sqlmysqldatabase-designdata-modelingdry
Gandalf
Gandalf

Reputation: 13683

Mysql how to avoid repeating myself

I have a table students with the following 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?

Upvotes: 0

Views: 150

Answers (4)

DRapp
DRapp

Reputation: 48139

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

Upvotes: 1

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

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.

Upvotes: 1

corrodedmonkee
corrodedmonkee

Reputation: 373

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.

Upvotes: 1

Josh Anderson
Josh Anderson

Reputation: 6005

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.

Upvotes: 2

Related Questions