h table design

[jeremyapp]

Hi,

 

I'm pretty familiar with PHP/MySQL, but my knowledge of database design is a little lacking.

 

I'm designing a learning management system (a la WebCT if anyone has used it) and I'm not sure exactly how to best design the database for it.

 

What I need to do is have, among other tables, one for students and one for classes.  I assume that each class would contain the students assigned to it, and not vice versa.  What is the best way for me to do this?  If I had a single table containing all the classes, how could each class have it's own properties (there's no such thing as a sub-table, is there?)

 

Thanks for any help you can offer!

[fenway]

Actually, neither table would be related directly -- you'd have a third table linking students to classes.

[jeremyapp]

So, I have a database for a learning management system with the following structure:

-----------------------
        Classes
-----------------------
            |
            |
             ------------------>classID
            |
            |
             ------------------>className
            |
            |
             ------------------>formalName
            |
            |
             ------------------>staffName



-----------------------
        Students
-----------------------
            |
            |
             ------------------>studentID
            |
            |
             ------------------>studentName

-----------------------
 Students_Classes
-----------------------
            |
            |
             ------------------>studentID
            |
            |
             ------------------>classID

 

I'm not sure if this is how it is supposed to be set up, but I use the Students_Classes table as a relationship table so that I can assign students to classes.

 

What I need to do is build a query that takes the student ID as an input and returns the following information: classID, className, formalName, staffName (all data from the "Classes" table) for each class that the student is in.

 

What would such a query look like? Thank you so much for your help!

[fenway]

You'll need to join all three tables together:

 

select c.*

from students as s

inner join students_classes as sc using (studentID)

inner join classes as c using (classID)

where s.studentID=<yourID>