ambiguity problem

[Brains86]

Hello, 

I was wondering if someone could help me with the following problem. 

I hava a dataset from my CRM which gives me 2 tables: "offerte" (offer) and "opdracht" (project). This holds the basic information such as total value, company_id etc.. 

For more detailed information about the offer or project I have to reach out to "onderdeel" (offerprojectline). This table holds the information of what specific product is on the offer/project or what is the buyingprice. 

The "onderdeel" table has a colomn named offerprojectbase_id. This colomn has both "offerte" id's (colomn1_id) and / or the "opdracht" id's (colomn1_id).  Therefore I connect offerprojectbase_id with the offer and project id's. 

To see in my data whicht company has how many offers and or projects I want to connect the  2 tables: "offerte" (offer) and "opdracht" (project) to the company table (with company_id). If i connect de "opdracht" (project) table with the company table there is no problem. But when i connect the "offerte" (offer) table with the company table I get an ambiguity problem. 

visual of my tables 

the problem: 

I can't figure out why, because the data in "offerte" id's (colomn1_id) and / or the "opdracht" id's (colomn1_id) do not match. 

"offerte" id's (colomn1_id) 

1

2

5

6

"opdracht" id's (colomn1_id)                              

3

4

7

8

"onderdeel" id's offerprojectbase_id

1

1

1

2

3

3

4

5

5

5

6

7

8

8

How can I fix this so I can connect the "bedrijf" table to both "offer" and "project" whereby they connect to the "onderdeel" table?

[Brains86 · Accepted solution]

I solved my problem with using a "help" table to combine the offerte and opdracht id's with their id's. 

See picture: 

[sayaliredij]

onderdeel table and offerte table has many to one relation.  If we consider onderdeel is your dimension table and offerte/opdracht is your fact table. Then the relation from onderdeel to offerte/opdracht should one to many (Not many to one)

once you do that and put direction from onderdeel to offerte/opdracht . you can solve the ambiguity problem

Regards,

Sayali 

If this post helps, then please consider Accept it as the solution to help others find it more quickly.

[Brains86]

Thx for the quick reply but my data looks like this. I was a bit to quick with that. 😄

"offerte" id's (colomn1_id) 

1

2

5

6

"opdracht" id's (colomn1_id)                              

3

4

7

8

"onderdeel" id's offerprojectbase_id

1

1

1

2

3

3

4

5

5

5

6

7

8

8