settingsLogin | Registersettings
Show Menu

Bunch of tables as satellite?

0 votes
Hi folks!

I have two tables: employees and positions. Tables positions and employees relate by the position nk as a foreign key on employees. Consider position is not a hub but only an employee attribute normalized to a side table.

You can have a look at the case:" rel="nofollow" target="_blank"> .

"Of courselly", employee splits into a hub, h_employee, and a satellite, s_employee.

Question: How do I deal with the position description, the attribute? Should I join employee to positions and then load the satellite as if from a larger table? Or would it be more correct to have a second satellite, holding a single attribute?

Yet another possibility: Load the position_id as an employee attribute to the satellite, and then load the key-value id/position to a decoder table (alongside some timestamp.)

What are your opinions, gentlemen? I've looked into the book (Building...) and the item 12.2.3 (page 511) says a decode table can be used, but not what would be a best practice. Like, what might be the largest hurdle over time: Maintain JOINs to load satellites or to manage a decode table?

Thanks ye all and best regards,

asked Aug 3, 2016 in Modelling by fabio (410 points)

3 Answers

+1 vote
Best answer
Good question!  I have the same doubt.
Well,we probably need to take the table 'position' as a hub and then create a link associating the employee(Hub) and the position(Hub), which means that the employee takes the position(some employee may be just on board and has not been assigned a position yet.So the Hub position should stand on its own.)
answered Oct 22, 2017 by neiltang (540 points)
selected Oct 22, 2017 by fabio
Thank you very much for your answer, Neil. It now seems the logical option: Position is a key business subject in compan, since people pursue careers and change places along the time. So from the DV theory point of view, position must be a hub, there must be a link between employee and its positions and, casually, a satellite to this link to track ups and downs. Although I liked Jacob's answer very much for its intelligent way of solving things, I am checking your as the most correct one.
0 votes
duplicate - @moderator please remove
answered Oct 22, 2017 by simco_admin (660 points)
+1 vote
Hi Fabio,

I probably would load it as a second satellite of H_Employee, since it is from a different source table.

You still need to track changes of the source data, and therefore you need them both.

The SAT_Employee would hold position_id along with other attributes,
while the SAT_Employee_Position holds position_id along with the description.

You can handle them separately when loading the satellites, so no dependencies there.

If the employee position descriptions are sourced from master data you might want to go with the reference table approach.

Since you look at item 12.2.3 I assume you do not have the requirement to track changes for this data.

I personally like to truncate the tables and reload them instead of using a merge described in the book, however the merge ensures that no data is removed from the table which could be a requirement.

I hope this helps!
Jacob Siemaszko
answered Oct 22, 2017 by simco_admin (660 points)
Thank you very much, Jacob. Your answer makes a lot of sense and it seems to me to be a very intelligent choice. However, from the point of view of Data Vault concepts, I believe Neiltang's answer is more aligned to the theory. That's why I am picking his answer with correct, although I liked yours very much.
Scalefree Scalefree

Upcoming Trainings

  • July 01 to 03 - Amsterdam (English) Data Vault 2.0 Boot Camp and Certification

  • August 05 to 07 - Berlin (German) Data Vault 2.0 Boot Camp and Certification

  • September 9 to 13 - Hanover WWDVC EU

  • September 16 to 18 - Brussels (English) Data Vault 2.0 Boot Camp and Certification

  • October 21 to 23 - Hanover (German) Data Vault 2.0 Boot Camp and Certification

  • November 18 to 20 - Amsterdam (English) Data Vault 2.0 Boot Camp and Certification

  • December 02 to 04 - Dusseldorf (German) Data Vault 2.0 Boot Camp and Certification

  • December 16 to 18 - Zurich (English) Data Vault 2.0 Boot Camp and Certification

  • Contact us for inhouse training engagements.
    Visual Data Vault: Logical Modelling for Data Vault - Free Download!
    Scalefree is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 United States License.
    Permissions beyond the scope of this license are available in our license. | DWH Wiki | Recent questions RSS feed | Imprint | Provided by Michael Olschimke