Hi there we have the concept of having department. each department can either be its own head or it can have a head department. (similar to each person in a organisation is a employee but some employee they have head/supervisor or some employee they act as a supervisior for a certain time that can also change over time if employees are leaving)
so we have a dim_department (SC2)
so it is an SCD2 which can slowly change over time if the name is changing ect
so let's say the dep1 is now becoming the head of the dep2
later there will be another department "dep3" be open and this will then become the head
so the head of dep2 is changing
what would be the best approach to model that
I guess there are several options/solutions to solve that problem
so I could
first option
add the head SK in the dim_department
so every time a Head is changing it would lead to an entry in dim_customer in our case a new entry for dep3 would be created and a new entry (slowly changing entry) for dep1 with a closing old current_yn and creating new current_yn
second option I thought
create a bridge table or factless fact table
or am I totaly wrong?
a third option would just be to put the Head SK in the fact tables
so we would than have
another options
a snowflake dimension ? hmmm wondering if that would work with an SCD2 dimension?
so in a nutshell it is on this SCD2 table a relation with itself
what I see as a problem with option 1 is that I have first to handle the HEAD entries because the attributes could change (because of changing name etc) and would generate a new SK and than I would need to lookup that key when handling the sub
what do you think?
is this total bullshit or totaly on the wrong path?
Thx
Best Answer
All you need is your one-to-many bridge table, and it really only needs the
head_dep_key
field and thedep_key
field. That can be joined back to yourdim_department
table when needed and should be able to answer all questions you'd ask of this data.