CODEWITHSUNDEEP
phpmysqldatabase
BruceyBandit
BruceyBandit

Reputation: 4324

Is this database structure correct or is there a better way?

I have got three tables, Student, StudentClass and Class table. The Student Table shows Student Information, The class Table shows the classes that goes on in the university and The StudentClass table links the students to their classrooms. Problem is though that if I create a query I get duplicate rows in the query if I join all three table. So what my question is that am I right to create a student and classroom database like this with these three tables or is there a more efficient and better way of doing this. There are other tables but I want you to just concentrate on these three tables. Thank You

Below are the tables:

Student Table:


StudentId(PK) StudentForename     StudentSurname    Year   StudentUsername    CourseId(FK) 
S1            Mayur                  Patel            3       u0867587          INFO101
S2            Jim                    Carlton          3       u1231231          INFO101
S3            Ahmed                  Seedat           3       u0660663          INFO101
S4            Amar                   Barot            3       u0954857          INFO101
S5            Richard                Davies           3       u0877223          INFO101

StudentClass Table:

ClassId(PK)  StudentId(PK)
101        S1
102        S3
103        S1
104        S2


    Class Table:

ClassId(PK)    Room      ClassDay      ClassTime      ModuleId(FK)      CourseId(FK)      TeacherId(FK) 
101          CW4/10     Thursday        10:00:00      CHI2550          INFO101             T1
102          CW5/01     Wednesday       12:00:00      CHI2565          INFO101             T5   
103          CW2/04     Monday          15:00:00      CHT2520          INFO101             T2
104          CW4/10     Thursday        11:00:00      CHI2550          INFO101             T1

Query Below: (THERE WHERE CLAUSE SIMPLY LOOKS FOR DATA ENTERED IN TEXTBOXES FROM A FORM)

SELECT * FROM Module m
            INNER JOIN Class cl ON m.ModuleId = cl.ModuleId 
            JOIN Teacher t ON cl.TeacherId = t.TeacherId 
            JOIN Session s ON m.ModuleId = s.ModuleId
            JOIN Grade_Report gr ON s.SessionId = gr.SessionId
            JOIN Student st ON gr.StudentId = st.StudentId
            JOIN Course c ON st.CourseId = c.CourseId
          WHERE
            ('".mysql_real_escape_string($sessionid)."' = '' OR gr.SessionId = '".mysql_real_escape_string($sessionid)."')
          AND
            ('".mysql_real_escape_string($moduleid)."' = '' OR s.ModuleId = '".mysql_real_escape_string($moduleid)."')
          AND
            ('".mysql_real_escape_string($courseid)."' = '' OR c.CourseId = '".mysql_real_escape_string($courseid)."')
          AND
            ('".mysql_real_escape_string($classid)."' = '' OR cl.ClassId = '".mysql_real_escape_string($classid)."')
          AND
            ('".mysql_real_escape_string($teacherid)."' = '' OR t.TeacherUsername = '".mysql_real_escape_string($teacherid)."')
          AND
            ('".mysql_real_escape_string($studentid)."' = '' OR st.StudentUsername = '".mysql_real_escape_string($studentid)."')
          AND
            ('".mysql_real_escape_string($year)."' = '' OR st.Year = '".mysql_real_escape_string($year)."')
          AND
            ('".mysql_real_escape_string($grade)."' = '' OR gr.Grade = '".mysql_real_escape_string($grade)."')

Upvotes: 0

Views: 92

Answers (2)

mergeconflict
mergeconflict

Reputation: 8266

Your schema looks fine, but your query is much more complicated than necessary. Try the following:

select *
from Student s, Class c, StudentClass sc
where s.StudentId = sc.StudentId
and c.ClassId = sc.ClassId

The inner joins are implicit in this query; it should give you four rows corresponding to the rows in StudentClass. Note that you'll see Mayur Patel twice: once in CHI2550 on Thursday, and once in CHT2520 on Monday.

If you want other filtering based on form fields, those will just be extra and clauses.

Upvotes: 1

duffymo
duffymo

Reputation: 308753

Your query will look something like this:

select *
from Student as s
left join 
StudentClass as sc
on 
s.StudentId = sc.StudentId
right join 
Class as c
on
sc.ClassId = c.ClassId

Upvotes: 0

Related Questions