Data Vault: links and reference tables

Viewed 324

I have a hub called h_company and connected to it satellite s_company with all descriptive information about companies in my DWH.

In my source database I have a mapping table called company_branch, which maps company to some branch(one column is company_id, second column is branch_id). Important note: branch_id column doesn't exist in satellite s_company.

I have a third table in my source database called branch, which consists of branch_id and description column. This would be a perfect reference_table, since it doesn't contain a lot of records(30 branches only), it doesn't change a lot and has a structure of reference table (id and description column).

My Question is: what is the best way to model company <--> branch relationship?

  1. Ideal would be a situation, if satellite s_company had branch_id column, then basically I could model branch table as reference table in my DWH and join later on the s_company satellite. But unfortunately, branch_id column doesn't exist in s_company satellite.

  2. My approach is to model company_branch table as link, that connect h_company and h_branch(created from company_branch table) and create s_branch satellite(created from branch table). But this approach looks like overkill, since branch isn't really a business object and just additional information(perfect reference table) and branch satellite would contain only 30 records.

How would you model company <--> branch relationship in this case?

Tables:

h_company columns: company_id, technicall_columns

s_company columns: company_id, desctiptive_columns(without branch_id),technicall_columns

company_branch columns: company_id, branch_id

branch columns: branch_id, description

0 Answers
Related