Hi. I’m trying to model employee time cards in Oracle in data vault. Can anyone give me some advice around the conceptual design, and would transactional links be best suited for this situation.
I imagine that similar to an order model, where you an order and order line items, I will want to separate time cards and time card entries in some way.
You can see that each time card entry has the days pivoted, and each value can (potentially be changed/updated over time) including the project, task, expenditure, and quantity.
Why T-Links may I ask? These have very specific use cases.
I suppose that’s part of the question, should I?
My understanding of standard links in this case would be employee has a time card which is approved by their manager.
Therefore link would reference the employee and manager.
However I would end up with links with a smaller level of granularity than the source data, i.e. unique hub references.
If I put the start date and end date into the link (each time card is a week) then I should retain the same level of granularity compared to my source.
So, I guess the question is: can I put dates into a standard link, and do I have them there as-is shown as an attribute in the link record, or do I have a hash key referencing a date hub?
I could do the same with time card entries referencing the time card entry.
I also thought that t links were for unique transactional events, as opposed to unique relationships, thinking that t links might be a better way to go.
Why not use a Link-Satellite?
What you would be creating by adding start and end dates into a link is not a t-link at all, it’s not even data vault.
T-Links have been renamed as non-historised links and are insert-only structures meant to support stream data.
As for the hubs, are you planning two separate hubs or a single employee hub? If the latter then you’d have a hierarchical link supporting the relationship with a link-satellite containing the details about that relationship.
I’d use a standard link with link satellite. You can’t be sure the timesheet entry would not be updated. And if you had other timesheet feeds in the future they would slot right in as new satellites.
You can use degenerate keys to help set a grain for the link (e.g. Task, Expenditure Type). You might need to use a multi-active satellite if it is difficult to get a business key at the right grain. You could also consider normalising the dates further (1 row per day in the satellite) or keeping a 7 column satellite. 1 row per day allows for easier roll up calculations for periods other than a week.
1 Like
