Thank you for taking the time to reply and thank you for providing the reasoning as to why you prefer to use the dependent-child key in the satellite table instead of the link table. These seem to me be valid reasons for the choice.
Are you suggesting that I should remove the dependent child-key entirely from the LINK into the LINK_SAT?
As I have just commented in my reply to Andreas, the main reasons for wanting to include the dependent child in the Link are 2-fold. The ScheduleOfRates (like Product) link to the WorksOrderLine and not the WorksOrder so the granularity of the relationship I require is at the WorksOrderLine level. Secondly, from my understanding, LINK tables are used as a basis for FACT tables in the InfoMart layer and I need my FACT table to be at the WorksOrderLine granularity.
If we take my second point in isolation for a moment, if i was to move the dependent child key from the LINK and into a SAT then the basis of my FACT table in the InfoMart would be the SAT, rather than the LINK, and I would join to the LINK table only to return the required surrogate keys for the relationships in my Star Schema. The granularity of my FACT table would be coming from the SAT rather than the LINK. Is this an acceptable position?
With regard to my first point, the ScheduleOfRates (like Product) HUB has a relationship to the WorksOrderLine and not directly to the WorksOrder (although as the parent of the Line it does have a weak relationship) so the granularity of the relationship I require is at the WorksOrderLine level.
Can I remove the WorksOrderLine from the LINK and still maintain the weak relationship of WorksOrder to ScheduleOfRates?
If so, then I would be able to move the WorksOrderLine to the LINK_SAT as a dependent-child key and take advantage of the benefits you listed above (if it’s OK to base FACT tables predominantly off a SAT as described in my earlier point). However, in order to determine which WorksOrderLine (dependent-child key) relates to which LINK_HASH_KEY in the population of the LINK_SAT I would now need to join on WorksOrder and ScheduleOfRates, from the WorksOrderLine, which would not guarantee me the uniqueness I achieve with WorksOrderLineID. So, I think i’m back to keeping the WorksOrderLineID in the LINK in order to ensure uniqueness (a WorksOrder could have multiple WorksOrderLines with the same ScheduleOfRates).
My initial question was whether I can partition on dependent-child keys, and, thanks to Patrick’s book ( The Data Vault Guru a pragmatic guide on building a data vault, Patrick Cuba pages 234-244) I have been able to determine that the answer is yes. Thank you Patrick, I’m finding the book extremely useful and informative and would definitely recommend it to others.
With regard to my particular requirement regarding the WorksOrderLine and the LINK I think i’m still a little turned around…