0

Payments

PaymentRef Product PaymentAmount MerchantRef
P01 ABC 100 MR01
P02 ABC 200 MR02
P03 XYZ 200 MR03

Refunds

ReundRef Product PaymentAmount MerchantRef
R01 ABC -10 MR04
R02 ABC -100 MR05

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 SUM() 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.

3
  • 2
    It definitely sounds like you'll need to change the table design to accommodate the new requirement - can you provide the existing PKs/FKs and any unique constraints declared on the tables?
    – bbaird
    Feb 5 at 15:10
  • can you give us the desired output table after the join operation?
    – MBuschi
    Feb 5 at 15:31
  • You probably want to remove the Product FK on Refunds and add instead an FK to Payments that way you will know which Payment each Refund refers to. For old refunds, do the join on MerchantRef as a one-off just to get the new FK. Feb 7 at 4:27
0

For your existing data, you are out of luck in terms of reconciling payments and refunds unless you have additional columns that you haven't included or external data (e.g. this is information from the payment processor, but you also have data from the system that initiated the payment which includes both the Merchant Ref and some other data, like customer id).

Moving forward, you probably want a "reference" column in Refunds that you store the MerchantRef from the associated payment. This will allow you to cross-reference (think in terms of how you would do it manually before considering how you would do it technically). Another alternative is to format the MerchantRef so that is it a derivative of the payment. E.g. Payment P01 would use P01 for the MerchantRef and any refunds from it would start with P01, followed by something unique to the refund (e.g. P01R1, P01R2 could be two refunds associated with payment P01).

In terms of working with the data you have, if you are looking at getting totals by, say Product, you can UNION the two tables then aggregate them using GROUP BY. Here is an example:

SELECT
    Product,
    SUM(PaymentAmount) AS TotalPaymentAmount
FROM 
(
    -- There are more columns than strictly needed for the aggregation
    SELECT 'Payment' AS RefType, PaymentRef AS 'Ref', Product, PaymentAmount, MerchantRef FROM Payments
    UNION
    -- Columns must match type
    SELECT 'Refund' AS RefType, PaymentRef AS 'Ref', Product, PaymentAmount, MerchantRef FROM Refunds
) Transactions
GROUP BY Product

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.