Hi all! This seems like a simple and common situation but we’re struggling with the right way to do it. We have a customer hub, and an invoice header hub. Invoices are linked to customers, and invoices come from multiple source systems (as do customers). Depending on the source system, there may be multiple relationship types between an invoice and a customer. For example, in one system an invoice has a bill-to customer and a ship-to customer. In another, it might have sold-to and ship-to, and there is some difference between the meaning of bill-to and sold-to so we want to track them as separate relationships. Another system just has a single customer for an invoice. New source systems will likely be added in the future and they may have their own relationship types for invoice-customer. A single customer can have multiple relationships with an invoice. For example, a single customer may be both the ship-to and bill-to customer for a particular invoice.
Our initial thought was to just create one link table with all the relationships that exist between and invoice and a customer, and store the details about which customer is of which type in an invoice satellite. But this seemed unsatisfactory as it’s storing information about the link in a separate entity. Another thought was to have a multi-active satellite hanging off the link with the relationship(s) that customer-invoice link represents. Yet another was to include a child dependent key in the link table with the relationship type, however that would need to be incorporated into the link key because like I said a customer can have multiple relationships with a single invoice. We’ve also considered multiple links, one for each relationship type. We’d need to add more if new relationship types are added in the future.
Any thoughts on this? Has anyone handled this before? If so, what did you do, and would you do anything differently?
Hey Chris,
We have a similar situation in our vault, it’s currenlty being handled as multiple links as they represent different relationships in the mind of the business.
We don’t anticipate needing more than two or three links to describe the relationships and new sources should comply with these relationships in the future unless the business definition of the relationship changes.
If there are really going to be a good many different relationships between invoicing and customers then it might be a question about whether the business really treats these relationships differently or not.
Would be interesting to hear about how others have modelled this situation as well!
All the best,
Frankie
2 Likes
Frankie,
Thanks – that does seem like a reasonable solution. Our modeling group has a preference for solutions that are less likely to require future changes to the vault model as business needs change, so that’s why we considered options that can flex to accommodate more relationships. But you’re right, there aren’t that many that could exist. 4-5 maybe? Right now I think we’re aware of four.
What I think we’re landing on in our case is a link table that is keyed on the the combination of customer key, invoice key, and a dependent child key. Still not sure if it’s good practice to include this child key in the link key – searching this forum produces arguments both ways – but if we want to use dependent child keys we need to include them in the link key because the same customer and invoice can participate in multiple relationships at once. But I might make the case for separate links one more time.
Anyway, thanks again!
Chris
(Also, if anyone else has thoughts on this I’d appreciate it!)
2 Likes
Forcing all relationship types into a single link table should be anti-pattern, adding dependent-child keys in a link too. Both introduce so many complexities for the querying of that data why not have them as separate link tables? You have decided to do this and I think that is the simplest implementation, the other introduce technical debt.
1 Like