I want to create a data model with a few n:n relationships
- A contact could be assigned to various companies and a company could have various contacts
- A table could contain addresses that can be linked to either companies or contacts, so that when the address changes, it is automatically changed for relevant contacts and organizations
I would like to add some "Metainformation" per company and contact link or address to contact or company link, such as
- Primary organization, Former employer, Board-style membership etc. for the company/contact relations or
- Primary address, Invoice Address, Corporate Address, Visiting Address, etc. for the address relations.
I also may want to add just a remark or a tag to the link, which is why connections (I think) would not work. It also does not feel right from a design point of view. (Let me know if I am wrong, here).
When a n:n relationship is created in dataverse, a relationship table (e.g. contact<->company) is automatically created.
It would be in my opinion the most elegant to just add a column to the relationship table storing this information.
However, this does not seem to be possible (grateful for any advice that proves this assumption wrong :))
Another option would be to create an additional table with the GUID of the relationship table and the metainformation as additional columns, but I also do not know how to do this.
The "brute force" approach would be to create the relationship table manually and have 1:n relationships to the two tables I want to join. This would - however - require to pass to a canvas app instead of a model driven app.
Question: Is the last approach (creating the relationship table manually) the way forward or ist there something more 'elegant' exploiting the functionalities of dataverse and powerapps.
