jeremyapp Posted September 2, 2008 Share Posted September 2, 2008 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! Link to comment https://forums.phpfreaks.com/topic/122407-h-table-design/ Share on other sites More sharing options...
fenway Posted September 2, 2008 Share Posted September 2, 2008 Actually, neither table would be related directly -- you'd have a third table linking students to classes. Link to comment https://forums.phpfreaks.com/topic/122407-h-table-design/#findComment-632324 Share on other sites More sharing options...
jeremyapp Posted September 4, 2008 Author Share Posted September 4, 2008 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! Link to comment https://forums.phpfreaks.com/topic/122407-h-table-design/#findComment-633309 Share on other sites More sharing options...
fenway Posted September 4, 2008 Share Posted September 4, 2008 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> Link to comment https://forums.phpfreaks.com/topic/122407-h-table-design/#findComment-633553 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.