0

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?

  • researchers can add collaborators to studies So collaborator have 2 referential attributes - created_by (ref to researcher, 1:M) and added_to (ref to study, 1:M). studies can borrow surveys from other studies So you add M:N relation between studies and surveys which needs additional table (survey_id, borrowed_by). The study which this survey is borrowed from is defined by survey_id and survey-to-study reference. If the study can be re-borrowed than the relation between studies and surveys is fully converteed to M:N, and survey is not study attribute. – Akina Jul 24 at 13:45

Your Answer

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

Browse other questions tagged or ask your own question.