Table design to store Refund Transaction information

Question

I have a transaction table that contains purchases and refunds. I'd like to create a linkage between purchase and refunds in the same table (I decided not to use a separate table). I'm not sure which direction the linkage should go. (One purchase can only have one refund - this is guaranteed)

• Option 1 - Refund is linked to the purchase

TrxID	amt	refundedTrxID
1	10
2	-10	1

• Option 2 - Purchase is linked to the Refund. (easy to see if a trx has a refund and which one it is)

TrxID	amt	refundTrxID
1	10	2
2	-10

Is there a standard way of modeling this? Or does it not matter since it's basically the same join?

SQL Server, but applicable to RDBMs

Answer 1

I would go work option 1. You will always have a purchase, but not always a matching refund.

This way you could foreign key refund to an existing purchase on purchase_id; to ensure valid purchase exists for the refund.

Whereas with option 2, you could not foreign key purchase to to refund, as it would not exist yet. If you tried to go this route, you’d have to insert a row into refund when that happens, and then also turn around and update purchase with that info.

As for how easy to see what has a refund or not, you can always create views for that.