1

I am trying to design a database management system with my PHP and intermediate MySQL knowledge, keeping normalization in mind.

These are the assumptions:

  1. The school has many students from grade 1 to grade 6.
  2. Each grade is divided into classes (e.g. we have grade 1 A, grade 1 B, grade 1 C) due to the class size.
  3. Grade 1 to grade 3 offer the same subjects, but from grade 4 to grade 6 users can select which courses to offer.
  4. Many teachers teach ONLY one subject BUT can teach MORE THAN one grade.

As an intermediate programmer, it all got twisted. I was able to create the following tables and columns

students{(id),(name),(name),(parent_id)} -student details

parents{(id),(name),(email),(phone),(address)} - parent details

teachers{(id),(name),(email),(subject_id)} - teachers details

subjects{(id),(name),(description)} - subject details

attendance{(date),(status),(student_id)} - attendance of student with id of student_id,
                                           status in boolean TRUE = present, FALSE=absent

This is how far I have gone.

Now I want to show a table where a student can be linked to all the subjects he/she is taking and a teacher linked to all the grades (no need to specify classes) he is teaching. How can I do that?

I will appreciate it if I can be pointed in the right direction.

Improve this question
4
  • What do you know about foreign keys? This will determine what the best way to answer your question is.
    – Walter Mitty
    Commented Jul 14, 2013 at 10:12
  • i know what foreign and primary key means and i can implement it. lets say a table for students {(id), (name), (parent_id)} and that of parent {(id), (name)} the parent_id in the student table is a foreign key.
    – Wilson
    Commented Jul 14, 2013 at 11:52
  • 2
    So far so good. Do you know how to design a junction table with two foreign keys so as to implement a many-to-many relationship such as between students and courses?
    – Walter Mitty
    Commented Jul 14, 2013 at 19:36
  • @WalterMitty thats is my problem, can you give me an hint? i have a single id {student_id} which is a primary key and i am to reference it in two different tables as foreign keys. i can only point one foreign key to it.
    – Wilson
    Commented Jul 14, 2013 at 20:24

1 Answer 1

Reset to default
1

Homework questions like this will generally not elicit answers with code. Stack Exchange is not a place where you can come to get others to do your homework for you. However you have only asked for pointers of where to look, so to that end, I will inform you that what you are looking for is called a many-to-many relationship, and requires a third table that maps table 1 to table 2. Usually, this third table just has two columns, each containing the primary key of one of the main tables, and the primary key of this table covers both columns.

Read up:

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.