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! Quote Link to comment Share on other sites More sharing options...
tmbrown Posted September 2, 2008 Share Posted September 2, 2008 ----------------------- Classes ----------------------- | | ------------------>classID <----------- Primary Key | | ------------------>studentID <-------- Foreign Key ----------------------- Students ----------------------- | | ------------------>studentID <----------- Primary Key This is only a partial schematic, there is much more that needs to be done to it. This is setup so that you can query SELECT * FROM Classes WHERE studentID = '{id}' Thus returning the list of classes in which the student is in, because one student may be in multiple classes. The same concept would go for the properties table, set the foriegn ID to classID in the properties table. Quote Link to comment Share on other sites More sharing options...
jeremyapp Posted September 2, 2008 Author Share Posted September 2, 2008 With this design, would I need a table for each class? I'm confused as to how I could link a student ID to a class if that weren't the case. Thanks! Quote Link to comment Share on other sites More sharing options...
tmbrown Posted September 2, 2008 Share Posted September 2, 2008 4 Tables: Classes Students Properties Relationships ---------------------- Classes ---------------------- classID---------------------------| | ---------------------- | -------------------------- Students |----------------------------- Relationships ---------------------- | -------------------------- studentID ------------------------| studentID classID Class table: Contains info about classes (classID = Primary Key) Student table: Contains info about students (studentID = Primary Key) Relationships: contains a student id with a class id in which that student is part of, may have duplicate student id's, but not duplicate student id's with duplicate class id in the same record(No Primary Key) Think of the Relationships table as a bridge between the Classes and Students table it allows us to cross over and see whats in each table. The properties table will simply be a relational table to the Classes table, the classID in the Properties table will determine which properties go with which classes The structure is Classes 1 class per row Students 1 student per row Relationships 1 student/class correlation per row Properties 1 class/property correlation per row Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.