CODEWITHSUNDEEP
mysqldatabasedatabase-design
Nima Amin
Nima Amin

Reputation: 92

MySQL Need Advice with my Table Structure - PHP

Basically, I'm looking into storing student course registration in a MySQL table.

The input would be something like this: 

Course Name       ####
Course Number     ####
Session           #
Year              ####

However, my dilemma stems from the fact that students take different number of courses and add courses as time goes by. Making it tricky to predict the number of columns and where to INPUT new data.

I was thinking of having one column named COURSES in my MySQL table and separating entries as such: COMM%121%1%2012, ECON%121%1%2012, COMP%121%1%2012, etc. So, it would be two delimiters, one for Course Name, Number, Session and Year (%) and another for the different courses the student is taking (,).

I'm open to any suggestions and/or critics. Bonus if you could also, include PHP querying tips for this type of structure or for the proposition you offer. Such as, selecting parts of one entry (Name, Number, Session and Year) as well as how would one go about updating a data INPUT if the student changes/drops a course.

Upvotes: 0

Views: 147

Answers (1)

Ivaylo Strandjev
Ivaylo Strandjev

Reputation: 70929

Your schema seems to be broken and that is why you can't figure out how to solve your problem. Student to course is a typical many to many realtionship so you need an additional table Student_courses that has foreign keys to both a Student and a Course. Adding columns dynamically is not a good option at all.

Upvotes: 4

Related Questions