Jump to content

Archived

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

Jakebert

[Database Design]

Recommended Posts

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!

Share this post


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

Share this post


Link to post
Share on other sites

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

 

Is there somewhere I could learn about that?

Share this post


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.

 

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

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


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.

 

 

Share this post


Link to post
Share on other sites

×
×
  • 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.