Very good discussion. How is it ultimately implemented?
Hi All, appreciate this is a really old thread and everyone has probably moved on with their lives, but on the off chance someone is still tracking this, I have this exact scenario and I’m struggling to work out how to model it.
I also have a transaction that refers to earlier transactions as a FK. My transaction table essentially brings together Account, Order, Product, Parent Transaction ID, Transaction ID, Amount.
I still do not see how this can be modelled to capture the relationship between the transaction if it’s in a Link table. I thought @nabav showed a lot of patience in replying to the specific feedback provided but seems we are missing a final response to their most recent message that might help clinch the answer.
Any advice?
Hi Mark,
Not familiar with the full context but the summary seems to be yes it’s absolutely fine to have a link reference one hub twice. Much like in the case of a same-as link, you can use this as a to essentially create a linked list of transactions instead.
I’m sure that was very limited in its use so do poke with more Qs on where more help is needed.
Cheers!
Frankie
Hey Frankie, really appreciate you getting back.
So in this example, there isn’t a hub for the transaction ID as it is modelled in the Link, either as a dependent child key in the link table itself or in the Link Sat as per suggestions above. But the transaction ID has a self referencing foreign key in that one transaction can refer to another. So I’m trying to work out how to build that noting the DV 2.0 course says you should not build Links on Links.
Any suggestions would be greatly appreciated.
Thanks
Does the parent transaction always have the same business keys associated as its child? In other words, will a parent transaction always be against the same combination of Account, Order and Product as its child transaction?
1 Like
No this can change. For example, the parent transaction may draw off one order, but the child draws off a different order
There are two options I’d consider:
Option 1:
Create HUBs for Account, Order, and Product, with a LINK connecting them. Then add a Link Satellite for transaction data, using the Transaction ID as the child-dependent key. If both the parent transaction ID and transaction ID are available in the satellite, you can query the relationship rather than embedding it in the model.
Option 2:
Introduce the hierarchy in a separate LINK with six Hub keys—one each for child Account, Order, and Product, and the same for their parent counterparts. This approach, which Cindy mentioned in her training, helps de-normalize a link-on-link structure. However, it likely requires storing child-dependent keys within the link.
In most cases, there’s no single “correct” solution. I’d suggest testing a few methods discussed here and, at each step, ensure you’re not breaking any principles and that your design solves the actual problem.
Thanks for taking the time to reply @dylan.roe . I think Option 1 looks like the go.
One last question, when you say create the Link Sat, using Transaction ID as the child dependent key, I assume that means the child dependent key is in the Link table not the Sat itself? This seems to be how it’s modelled in the training I did for DV 2.0 but I was wondering if in your option you were, in fact, suggesting the child dep goes in the Link Sat?
Thanks again
After chatting with some data vault peoples, we agreed to include the child dependent key in the link (and therefore in the Link HashKey). Just remember this means the granularity is at a transaction level rather than just the unique relationship between account, product, and order.
I would suggest keeping the transaction ID (child dependent key) in the satellite along with the parent transaction ID. This makes it easier to determine the relationship between a transaction and its parent when querying the raw vault.
Slightly different approach but someone suggested calculating the Link HashKey of the parent transaction and storing it in the link satellite. It wouldn’t be used for loading the satellite but could help jump back to the parent transaction’s record in the transaction link. It’s not an approach I would have thought of, but it shows there are multiple ways to solve this.
There was a hearty discussion about modelling a dependent child here: Modeling a dependent child - Data Vault 2.0 - Data Vault User Group Forum
2 Likes
This is terrific. Thanks Dylan. Definitely given us some food for thought on our design. I believe we will look to model the dependent child in the Link and model the IDs in the Sat for both child and parent. Thanks for sharing!