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?
Ideal would be a situation, if satellite
s_companyhad branch_id column, then basically I could modelbranchtable as reference table in my DWH and join later on the s_company satellite. But unfortunately, branch_id column doesn't exist ins_companysatellite.My approach is to model
company_branchtable as link, that connecth_companyandh_branch(created fromcompany_branchtable) and creates_branchsatellite(created frombranchtable). 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