I have a problem with relationships. I try to combine surveys from different sources about our 40 schools. Some surveys have questions about the school, other surveys have questions about the principals. We would like to compare average scores.
Some principals are connected with one school over many years. Other principals change schools or have more than one school to lead. Some schools are lead by more than one principle.
I would like to create a data model in which I can easily calculate scores for both schools and principals.
Would this be possible ? See attachment of what we want to do.
In data modelling, this is a classic case of "many-to-many" relationships. It is possible to do the modelling in these scenarios and it is quite elaborate to explain. So rather than me explaining it, there is a well written article on how to handle these scenarios using a "bridge table" by sqlbi.com.