Sample Header Ad - 728x90

best approach to model relation between customers - dimension dim_customer (SCD2) which can be a HEAD/SUB customer

1 vote
2 answers
228 views
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 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 "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 enter image description here second option I thought create a bridge table or factless fact table enter image description here 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 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
Asked by walpino (11 rep)
Feb 2, 2021, 10:51 AM
Last activity: Jun 8, 2025, 12:05 AM