Question

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: bin.co/2qJdD5cCublu.png" rel="nofollow" target="_blank">https://ibin.co/2qJdD5cCublu.png .

"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,


Fábio

Answer 1

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.)

Comments

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.

Answer 2

duplicate - @moderator please remove

Answer 3

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!
Greets,
Jacob Siemaszko

Comments

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.