[Database Design]

[Jakebert]

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!

[roopurt18]

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

[Jakebert]

I was with you up to the third table...

 

Is there somewhere I could learn about that?

[roopurt18]

http://www.tomjewett.com/dbdesign/dbdesign.php?page=manymany.php

 

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

[keeB]

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
}
?>

 

[roopurt18]

I wouldn't assume one professor / teacher per course.

[keeB]

There wouldn't be one canonical person who does the grading for a course?

[roopurt18]

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.

[Andy-H]

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.