Jump to content

[Database Design]


Recommended Posts



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:




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?



Link to post
Share on other sites

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

Link to post
Share on other sites

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.


class ReportCard {
    private $student; //fk to student
    private $instructor; // fk to instructor
    private $course; // fk to classes
    private $grade; // A, B, C, D, F


Link to post
Share on other sites

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.



Link to post
Share on other sites


This topic is now archived and is closed to further replies.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.