Bit column vs multiple join tables 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'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....