Data Modeling for Specific project

[May_24]

Good Greetings,

I have two fact tables. The first contains Sales Representative Expenses and other details, and the second contains Sales details.
I want to connect the shops in both tables to get the quantity sold from the sales system table, the first is written by the Rep. person and the second is from the sales system.
Actually, I connected it through a bridge table because it's a many-to-many relationship, but I am facing a problem. When the representative's name is the same in another brand or other branch it causes returning the whole shop's names from all brands or branches related to that name.
Can you help me solve this issue even if you suggest another connection way, please?

Please note that the Rep. Name doesn't exist in the Sales Table.

Thanks and appreciation.

[Anonymous]

Thanks for mh2587's concern about this issue.

Hi, @May_24 

You can refer to the method provided by mh2587 .

Or you can also add unique identifiers for Shops to differentiate between stores with different Brand and Branch.

Of course you can also add Branch ID and Brand ID in Shops-Rep-Dim to accurately map the relationship.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mh2587]

Just try the composite key by combining the RepID, BranchID, and BrandID. hope it will solve the issue

[May_24]

Thanks for your reply.

How do you suggest to make it?

If I put the branch ID and the brand ID in the Rep-Dim table, how should I connect it with the Shop-Rep-Dim table?