In my current schema I have researchers, studies, and surveys.
researchers create studies.
studies create surveys.
Right now, the survey table has a study FK (one-to-many) and the study table has a researcher FK (one-to-many).
I'm trying to introduce some new functionality. Namely, that researchers can add collaborators to studies. Also, that studies can borrow surveys from other studies.
There are two ways that seem obvious as potential solutions to both these additional requirements, but I'm not sure which one is better.
Way One: create a single M2M table between the two entities. For example a M2M researcher_study table that has researcher, study, and a boolean which records if this researcher is the creator of the study. Similarly, create a M2M study_survey table that has study, survey, and a boolean which records if this study was the one that created the survey.
Way Two: maintain the existing one-to-many relationships and add a M2M table which describes the new type of relationship. In the case of adding collaborators, a M2M collaborators table can be added between researchers and studies. In the the case of borrowed surveys a M2M borrowed_surveys table between studies and surveys.
Which solution would you adopt and why?