There was a similar topic a few months back where I was looking to see how best to deal with transactions that have headers and lines - such as a purchase order or invoice. At the time, this looked relatively straightforward to deal with using a Hub/Sat combo for the header and a Link for the line (containing a child dependent key for the line number) with an associated Sat for the line detail.
Revisiting this it seems our lovely ERP system is more complex and has multiple descendants below PO line, all containing attributes of interest and none of which contain anything resembling a business key at the bottom two levels.
In Oracle ERP, it looks like as follows (simplified):
PO Header --< PO Line --< PO Line Location --< PO Distribution
I am trying to best deal with PO line locations and PO distribution, which have a mass of foreign keys and nothing resembling a partial natural key we can leverage. The FKs are all context dependent -so get set depending on what the PO header 'type' attribute is defined as.
I know Link on Links = bad. There are insufficient things resembling a friendly BK at line locations and distributions level to provide a driving hub. Even the combo of the FKs look like it might not be enough to drive uniqueness. PO Line Locations has a 'shipment num' that is business facing but it can and is repeated many instances per PO Line. Eek.
My current thought process is to deal with via multiple Link tables - at PO Line, PO Line Location and PO Distribution level - and associate with the Hubs I can identify business keys for - such as the PO Header.
As PO Line is a FK to each of these PO Line Location and PO Distribution tables I'm tempted to promote the PO Line from a Link with a child dependent key to a Hub (BK being PO Number and Line Number). I'm also thinking of using the ERP system surrogate key due to lack of choice as the child dependent key within the proposed PO Line Location and PO Distribution Link tables - each with an associated Sat to hold the descriptive attributes.
Presuming the PO Line is modelled as a Hub/Link, I am initially proposing the Link for PO Line Location has a single child dependent key (the surrogate line_location_id) and the Link for PO Distributions at this stage contains potentially TWO child dependent keys - the surrogate line_location_id from its parent and the surrogate distribution_id.
However, It seems ugly and I am not even sure if multiple child dependent keys are viable/allowed.
Would creating a Hub each for the line location and distribution based on the surrogate key just be the most pragmatic fall back, thus enabling a standard link to be created off each? I understand surrogate keys as BKs are allowed as a last resort - which I am thinking this probably is.
Thoughts? Is there a better way? I am in danger of suffering death by analysis and want to lock this down and move on!