Multi level relationship dynamics 365 (connections table)

Hi,

For one of our clients I am trying to create a report based on data from Dynamics 365. From the data model I am use the following tables:

- MultiYearPlan (custom entity)

- Account

- Account_multiyearplan (to define the many-to-many relationship)

- Opportunity

- Opportunity_multiyearplan

- Multiyearplan

- Connections

The issue I have right now is that in the connections tables relationships are defined from account to account, account to opportunity and opportunity to account using the columns: Id1 and Id2. Both entities can be defined in both columns.

What I am trying to accomplish is to create a report that shows all the accounts and opportunity's that are related to a multi yearplan. The triky part is that a account can also be related to a multiyearplan through another account. Wat I try to achieve is to get all the account that are related to a multiyearplan eather directly (trough the account_multiyearplan table) or throught a relationship in the connections table with a account that is related to a muliyearplan in the account_multiyearplan table.

I am trying to figure out how to create a data model to achieve what my client is looking for. Any imput would be really appreciated. Thank you!