0

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.

Improve this question
1
  • 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, 2013 at 12:29

1 Answer 1

Reset to default
1

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
1
  • 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, 2013 at 19:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.