I asked a question earlier about how I could define a many to many relationship between users and FOSes (fields of study). My original question was how could we know whether the user was the instructor or the user was the student.

My original idea was two junction tables, one called user_instructor_fos and one called user_student_fos.
Another solution was to use a bit column inside a single join table called is_instructor. If the user was the instructor this value would be set to one.

I can see how both solutions would work I was just wondering what the pros and cons of each solution are and if there is a best practice.

  • I think it would be better if you edited your previous question, adding this info, too. If you can add the designs you have (if possible, not only in words but with either SQL or a graphic model) it would be great, too. (and then delete this question.) – ypercubeᵀᴹ May 29 '13 at 12:29

I'd have role table that contains "student", "instructor", etc. Then join table would look like user_role(user_id,role_id, fos_id, date_from, date_thru) . Date_from and date_thru may or may not make sense for your task....

| improve this answer | |
  • I like the date idea, conceivably over time someone could be both a student and then an instructor and in reporting you might want to know which one he was at various points in time. One of the biggest weaknesses in many database deisgns is that they don't consider how the data will change over time and what that means for how they intend to use the data (especially in reporting) because the designers are often only concerned with data entry. – HLGEM May 29 '13 at 19:24

Your Answer

