Avoiding Mulit to Multi relationship

After watching a lot of the videos, i’d really like to embrace Sam’s recommendation on how to layout the tables, avoiding bidirectional and having waterfall layout.

I have some data

  • Staff table (Dim) 1 row per Staff member

  • Team table (Dim) 1 row per Team member

  • Dates table (Dim) 1 row per Date

  • Staff to Team Mapping table - contains which Team a staff member was in on a given day. I’m assuming this is a fact table, where the three columns link to the above dimensions

  • Task table (Dim) - 1 row per task

  • Task Staff mapping table - this has 1 row per task and contains the TaskID, the StaffID and Date so we know which Staff member was working on the Task each day

  • Client table (Dim) - 1 row per Client

  • Task Client mapping table - this has a row for each task to client combination. This does not contain the date. A task can have many clients and a client can have many tasks. I am assuming this is a fact table.

This would mean 3 fact tables

My Question:
If i set the model up like that my issues includes the fact that there is no link between staff and client, e.g. if i want metrics on what staff and teams have been working on my top client then i dont think my model would support it.

What I am looking for: Maybe its a video or somewhere an example like this has been discussed, so i can review in relation to my issue. My issue is somewhat more complex than this theoretical example.

some good techniques have been discussed in this forum post - please see if one of these solutions can help.

If not, then a mock up of your model might be needed to better understand your specific use-case.

In addition to the suggestions already made by @Heather here’s another thought

I would consider that to be a slowly changing dimension, type 2 is commonly used which means you include an idenifier/flag for the ‘current’ value.

.

Here’re some links to datamodelling related content.

1 Like

Hi @StuartFlint did the response provided by the experts above help you solve your query?

If not, how far did you get and what kind of help you need further?

If yes, kindly mark as solution the answer that solved your query.

Hi @StuartFlint, we’ve noticed that no response has been received from you since October 15.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Hi @StuartFlint, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, please create a new thread.