Jump to content

Help with MySQL database design


jeremyapp

Recommended Posts

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
Share on other sites

-----------------------

        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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.