I have a very odd relationship in part of a legacy schema, was just wondering if anyone can see a way to map it directly.

Contract is 1 <-> 1 with BillingDetails (FK [ContractNumber])
Contract is 1 <-> 1 with Payer ClientRole (FK: [ContractNumber, 'PY'])
Contract is 1 <-> 1 with Mandate (FK: [BillingDetails.MandRef, Payer ClientRole.ClientNumber])

That is to say, Mandate has a composite key, and the values required to index into that composite key come from two different tables.

Sample SQL to retrieve the mandate details for a Contract would be:

SELECT
mand.bankkey,
mand.bankacckey
FROM
chdrpf chdr,
payrpf payr,
clrrpf clrr,
mandpf mand
WHERE
chdr.chdrnum = ? AND
payr.chdrnum = chdr.chdrnum AND
clrr.forenum = (rpad(chdr.chdrnum, 8) || '1') AND
clrr.clrrrole = 'PY' AND
mand.payrnum = clrr.clntnum AND
mand.mandref = payr.mandref


I think I'm going to have to define a view for this, but I'd be interested to see if anyone sees another way!

Thanks

Niall