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

  • any chance there will be a partial refund? if yes then 1st option works better in my opinion otherwise the second one need to a comma-separated value until you normalise it
    – Biju jose
    Mar 2, 2022 at 8:21
  • 1
    Will you have multiple refunds for the same purchase? Or multiple purchases refunded together? If the former then option 1, if the latter then option 2, if both then a third option is a many:many junction table containing the foreign keys of both as a composite primary key Mar 3, 2022 at 23:40

1 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.

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.