Handling relationship between parties in Data Vault

Data Vault 2.0
1

Hello All
In my current project, I am facing an issue related to handling the relationship between parties in Data Vault.

I need to model to cater the below requirement.

  1. There are two parties - Party (customer) 1, Party (customer) 2
  2. Both the parties can have one or more relationship.
  3. Party 1 => Party 2 using a relationship code 1
  4. Party 1 => Party 2 using a relationship code 2

Have created a hub HUB_PARTY having PARTY_HKEY as the primary based on the business key party identifier. Created a link LINK_PARTY_RELATED having PARTY_HKEY, PARTY_RELATED_HKEY(recursive).

I need to include relationship code to make it unique combination - PARTY_HKEY(based on party identifier 1), PARTY_RELATED_HKEY(based on party identifier 2), RELATIONSHIP_CODE.

Am not sure where to include the RELATIONSHIP_CODE. Can I add it to LINK ? Won’t it override the stateless behavior of links?

Please help me in this regard.

Thanks.

Swaminathan

2

Sounds like a dependent-child key; your choice nbow will be either to

  • include it in the link table - relationship type never changes
  • or use it in the link-satellite table in case there could be multiple dep-keys per relationship or if the relationship changes
3

@patrickcuba Thanks for the response. This really helps.

1 Like
4

You might also want to add an effectivity satellite to record validity period of the link.

1 Like
5

Don’t think you can in this situation? There’s no driving key here. Not without the dependent child key.

6

Needed to write s bit more here - yes, use the child-dependent key as well.

1 Like
7

Thanks Neil. Have added dependent child key and a satellite as well as well to capture validity.

8

For some reason I was thinking about this just now…

I think some other factors could be decisive for my personal choice, in the end.

If

  • there are only two different relationship types, and you know you’ll never get any new ones
  • the source provides no additional attributes, just the two parties and their relationship type
  • no deletes can ever occur in the source

then I might choose to create a link without any dep key, and create two separate effectivity sats, one for each relationship type. However, now that I’m typing this… you’d be filtering in your staging, but having only that link and the two eff-sats, there’s no room to store that relationship type as an attribute.

If deletes can occur, an eff-sat alone won’t help you, you’d need a status (delete) tracking sat as well. I think I would then opt for a regular link, a sat with dep-key, and an STS on the link.

Come to think of it, I think I would always go for the latter option.

So much for my first 2024 ramblings.