These tables used to be joined on
MerchantRef, resulting in a list of payments and refunds. This worked because only single refunds against a product were allowed. Multiple refunds are now allowed, meaning
MerchantRef must change for each refund (payment provider requirement) therefore breaking the join. If I change the join use
Product, I end up with duplicated rows.
Is there method or some trickery that will allow me to join on
Product at all? While
Product can exist multiple times, it always refers to the same thing. I don't think there's a way to proceed as we have no way of knowing which refund refers to which payment. The only approach I can think of to
PaymentAmount for all matching
Product records, and do the same for refunds, and do it based on total.
SQL isn't my forte at all, but am I right in thinking my only solution here is having a reference between the tables that never changes?
I'm trying to keep changes to a minimum, I never would have started with this design, but here we are.