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

data-warehousedatabase-design

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

Best Answer

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.