mercredi 15 juillet 2015

Schema design for users with many school classes

I am trying to add a new table called user_classes which will associate my user's classes. I am building a website that allows them to generate class schedues. Within my database, I have two existing tables - a users table which contains the id, name, and email:

=users table=                                                            
id    name    email                                                       
1     John    john@example.com   

I also have a classes table which contains all my classes. crn is simply the course number, and name is the name of the class.

=classes table=                                                              
id    crn    name                                                              
1     4000   CS 101                                                             
2     4001   CS 102                                                             
3     4003   CS 103  

This new table is what I am thinking. I have an id which is the PK, have an user_id column to say that John Doe has all the following crns.

=user_classes table=                                                         
id    user_id   crn                                                       
1     1         4000                                                      
2     1         4001                                                      
3     1         4003          

My question is, is the user_classes a good design? Instead of crn, should I use the id from classes instead?

In this schema, user has many classes. However, a class also has many users.

Aucun commentaire:

Enregistrer un commentaire