I'm trying to model a kind of INFORMATION_SCHEMA using data vault approach for detecting of data lineage and I'm a bit confused about how to implement the relationship between tables and their columns. Some columns have the same name, like ID or Name, but different description and meaning.
I would like to have 2 HUBs, one for tables and one for columns, to be able to create hierarchical links for each of there HUBs for storing these origin objects and formulas for transformations.
If i would create a HUB for tables and a HUB for columns, what should i use as a business key for columns, because the column names is not unique? If i put columns into a satellite table of HUB_TABLE using multi-active satellite modelling approach, i can not simply create a hierarchical link for this satellite.
Any better ideas or suggestions?
What is the best practice to model such kind of nested structures with 1:n relationships? (DB -> SCHEMA -> TABLE -> COLUMN)?