0

I am working on a system where you have users, and you have Fields of Study or 'fos' for short. Each user can have many fos and each fos can have many users. So we are dealing with a many to many scenario. Here is the rub, each user can have many fos they act as an instructor for, and can also have many fos they act as a student for. How is this relationship defined? Would you have two junction tables one like user_instructor_fos and one like user_student_fos?

  • Can a field of study have many instructors? Or just one? Or possibly zero (only 0 or 1)? – ypercubeᵀᴹ May 27 '13 at 15:34
2

You can simply add an "is_student" bit column to the relation "UserToFOS", and create a unique key on user_id, fos_id and is_student.

  • I can see how that would work now. It would appear having two join tables would work also. Pros and cons to each approach? – slick1537 May 29 '13 at 12:01
  • @KevinMcKinney: They seem about the same storage wise.. I guess querying might also be easier either way depending on your queries (that said fewer tables generally means easier maintenance with respect to indexes and such) – WrinkleFree May 30 '13 at 6:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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