One-to-many as Bridge

Hello,

our main Dimension has the following structure:

Company Dimension:

Division_Key
Division_Name
Company_Name
[..]

We have now a single case, where this is not sufficient. Our HR data fact table has the following structure:

Department_Key
Value
[..]

We don't need or report the department, only Division and Company. The mapping between Department and Division can Change.

Can we use a Bridge table for this:

Department_Key
Division_Key

If the mapping changes, I don't need to reload the fact table. All I have to do is to reload the Bridge.

I would solve this by maintaining both the current (Type1) and historical (Type2) values of the attributes in the dimension. This way you can report the facts at either state

eg
Department_Key
Division_Name
Historical_Division_Name
Company_Name
Historical_Company_Name

Thanks for your reply. Our Company Dimension is SCD Type 2/3. Problem is, that most of our fact tables have only the Division_Key and not the Department_Key.

Therefore I want to use a bridging table. But I am not sure if this is the best solution.