Jakebert Posted November 20, 2009 Share Posted November 20, 2009 Hi, I'm having a brain block figuring out how I'm going to arrange my database for an app I've been developing for a while (with several failed attempts). Here's the low-down: - Students take classes - Instructors teach classes - Students have report cards - Instructors write report cards I've tried having the following tables: students instructors classes report cards but the problem I end up with is when students sign up for more than one class. I can't figure out if students should belong to classes or the other way round. Can anyone think of an ideal design for this structure? Thanks! Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted November 20, 2009 Share Posted November 20, 2009 Students and classes are a many-to-many relationship; neither belongs to the other and you need to use a third table. students: id, fname, lname, ... classes: id, title, description... classes_to_students: student_id, class_id Quote Link to comment Share on other sites More sharing options...
Jakebert Posted November 20, 2009 Author Share Posted November 20, 2009 I was with you up to the third table... Is there somewhere I could learn about that? Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted November 20, 2009 Share Posted November 20, 2009 http://www.tomjewett.com/dbdesign/dbdesign.php?page=manymany.php http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html Quote Link to comment Share on other sites More sharing options...
keeB Posted November 20, 2009 Share Posted November 20, 2009 Instead of classes_to_students I'd store the data in the ReportCard table. This way you have a summary all in one table without an intermediary. The classes_to_students recommended by roopert seems a bit over the top for this case. <?php class ReportCard { private $student; //fk to student private $instructor; // fk to instructor private $course; // fk to classes private $grade; // A, B, C, D, F } ?> Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted November 20, 2009 Share Posted November 20, 2009 I wouldn't assume one professor / teacher per course. Quote Link to comment Share on other sites More sharing options...
keeB Posted November 20, 2009 Share Posted November 20, 2009 There wouldn't be one canonical person who does the grading for a course? Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted November 20, 2009 Share Posted November 20, 2009 From K12 through college, I've had several courses taught by more than one teacher or professor. I make no assumptions about how they divided up the grading amongst themselves. Quote Link to comment Share on other sites More sharing options...
Andy-H Posted November 22, 2009 Share Posted November 22, 2009 Personally I would go about it like this. >table student >>field studentID >>field stunentName >table tutor >>field tutorId >>field tutorName >table subjects >>field subjectID >>field subjectName >>field subjectDescription >table studentSubjects >>field studentID >>field subjectID >table tutorSubjects >>field tutorID >>field subjectID This is on the basis of:- [ tutor - subjects = one to many relationship ] [ student - subjects = one to many relationship ] [ studentSubjects determines relationship between student and subject using foreign keys ] [ tutorSubjects determines the relationship between tutor and subject using foreign keys ] Then you can determine the tutor - student relationships using common subjectID keys between them. I would also implement some sort of time management in there to ensure that students can't take subjects that overlap in lesson times and tutors can't be "double booked". Obviously this is the bare necessity of information required for your system. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.