Hello, I have a novice (hypothetical) question about a schema evolution scenario to gain a better understanding of how a data vault should be operated.
Let’s assume I’m developing a database for the human resources department of an IT-resales company. I initially create a simple model with HUB_EMPLOYEE having two multi-active Satellites:
This setup functions well for several years. However, at one point, employee certifications become a crucial aspect of the business (determining vendor discounts, etc.), prompting the need to expand that part of the schema.
My questions are about querying the certifications after the expansion:
- Do I need to continue joining the old SAT_CERTIFICATE to all my queries involving certifications indefinitely, or is there a more straightforward approach?
Furthermore, if the former is true:
- Is there a standard way to document which queries need to include such historical joins and which don’t? I imagine it might become somewhat confusing if there have been multiple schema expansions.
I suppose it depends if you still need to populate the old satellite table.
If you have refactored the content this way, does that suggest SAT_CERTIFICATE contains the certificate id you now want to be loaded to HUB_CERTIFICATE?
If so, it seems more like a refactoring and you could rather benefit from simplifying the model instead of “evolving” it in this way.
No need to keep populating SAT_CERTIFICATE with duplicate info after the expansion.
Do I understand correctly that the easiest course of action would be to copy all the existing data from SAT_CERTIFICATE to SAT_CERTIFICATE_INFO and drop the original SAT_CERTIFICATE altogether?
Probably – sounds like a refactor + migration
Looks like Patrick has provided you with your answer hopefully
Can i ask how you created your Data Vault relationship diagram? Which tool did you use?
Thanks in advance.
This was done by Microsoft Visio, using the Data Vault stencil from here: https://www.visualdatavault.com/
(With minor modifications to fonts, sizes and colours.)
That’s great. Thank you @Mart