best approach to model relation between customers - dimension dim_customer (SCD2) which can be a HEAD/SUB customer 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)
dim_department
<img src="https://i.stack.imgur.com/0og9P.png" alt="dep_key department_id department_name valid_from_date, valid_to_date, currenty_yn
1" />
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 &quot;dep3&quot; 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
<img src="https://i.stack.imgur.com/56XvU.png" alt="key
date_key
HEAD_dep_key
dep_key" />
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
All you need is your one-to-many bridge table, and it really only needs the head_dep_key field and the dep_key field. That can be joined back to your dim_department table when needed and should be able to answer all questions you'd ask of this data.