How to create hierarchy table of tables with many-to-many relationship?

[Sem_Valks]

In my datamodel below I want to have a relationship from the documents table to the Employer, Employee, Assignment and Taxservice table since a document can belong to one of those tables. Creating these relationships directly is not possible because it wil create ambiguous paths. Therefore my idea was to create a hierarchy table with the Employer, Employee, Assignment and Taxservice tables in it. From there I can create a relationship between the hierarchy table and the documents table. 
However, there is a many-to-many relationship between the Assignments and TaxServices tables. 

Is it possible to create a hierarchy table of multiple tables if there is a many-to-many relationship between two of them? Or is there any other way of creating a relationship between the documents table and the other 4 tables without creating ambiguous paths?

[rajendraongole1]

Hi @Sem_Valks - You can create a new table, called HierarchyTable, that contains the unique identifiers from each of these tables. The HierarchyTable should have columns like DocumentID, EmployerID, EmployeeID, AssignmentID, and TaxServiceID.

HierarchyTable with the relevant keys from each of the Employer, Employee, Assignment, and TaxService tables. The table should be structured so that each row represents a unique document linked to one of these entities.

lastly as mentioned document table, you can create a relationship between the Documents table and the HierarchyTable based on the common key (e.g., DocumentID).

For many to many you can follow the below scenerio:

Create a bridge table that resolves the many-to-many relationship between Assignment and TaxService. This bridge table can have columns like AssignmentID and TaxServiceID.

Hope the above information helps.

[Sem_Valks]

Thanks for your reply but this does not solve my issue entirely.

I can create the hierarchy table as you are mentioning which makes it possible to create a relationship between the documents and the parent, but in this situation I lose the relationship between the Employee, Employer, Assignment and TaxService tables. 

Can you make a schematic view of the datamodel that you are suggesting?

[rajendraongole1]

Hi @Sem_Valks - can you chehk the below model , i have used the approx column names

Hope it helps to design the same at your end.

[Sem_Valks]

With the model you suggest, I cannot create the desired report.

I would like to creat this datamodel:

However, this is not possible due to ambiguous paths. Therefore my idea was to create a big hierarchy table with parent/child relations which includes all Employer, Employee, Assignment and TaxService data, like below:

But I do not know how to create this hierarchy table since there is a many-to-many relationship between the TaxService and Assignment table. This means that one TaxService can have multiple parents which causes a problem when trying to create a hierarchy table without duplicating the taxservice rows for each parent.

Is there any solution to this case?