Typically there would be a reason why you'd give a refund, so already the schema for this use case is different than that of a purchase.
So now your choice is should you store the refund in both places? It always makes me uncomfortable having multiple sources of the truth.
You will need to decide how you are going to work out the overall balance of a customer, storing the in/out in multiple places is going to make this harder than it should be. So you're back to having a single store for money in/out and a separate store for meta-data about a refund.
There are a hundred ways to skin a cat, but here are a few "food for thought" points:
You could potentially add a "Refund" column that would contain a "1" if it is a refund, or a "0" for a sale. You then have to decide whether to keep the amounts all as positive values (and just subtract if there is a "1" in the refund column) or if you want the amounts to be positive and negative and just look at the refund column as more of an indicator (possibly for reporting purposes)
You should consider your purchaseID! Do you consider it more of a "transaction ID" or an "order number". It may seem like there is no difference at first, but a transaction ID would have a unique ID for every entry that would mean a purchase would be 0000, and the refund would be 0001 (for exmaple). If you treat it as an order number, the purchase would be 0000 AND the return would also be 0000 so that you know the refund is related to that specific purpose.
Expanding on my previous point, I would suggest considering a separate Refund table that would contain a unique RefundID, CustomerID, OriginalPurchaseID, ItemID, Amount, and Reason column (and perhaps PaymentMethod). Your Sales table would remain pretty much the same: (unique) PurchaseID, CustomerID, ItemID, Amount, PaymentMethod. Also, be careful with your ItemID as the current setup would require a separate entry (with repeated purchaseID) for EACH itemID.
Hopefully this helps a little bit and can guide you to a better structure. Don't be afraid of having multiple tables, just make sure that you have a good method of relating them!
发布评论
评论（2）
通常情况下，您退款是有原因的，因此此用例的架构已经与购买的架构不同。
那么现在您的选择是是否应该将退款存储在两个地方？拥有多个事实来源总是让我感到不舒服。
您将需要决定如何计算客户的整体平衡，将输入/输出存储在多个位置将使这变得比应有的更加困难。因此，您又回到了拥有一个用于资金进出的商店和一个用于存储有关退款元数据的单独商店的情况。
显然还有其他字段，正如您所说，退款的价值
碰巧这更接近现实世界的纸质分类账和单独的“退款”簿。
我从来没有这样做过，这只是我大声思考:-)
Typically there would be a reason why you'd give a refund, so already the schema for this use case is different than that of a purchase.
So now your choice is should you store the refund in both places? It always makes me uncomfortable having multiple sources of the truth.
You will need to decide how you are going to work out the overall balance of a customer, storing the in/out in multiple places is going to make this harder than it should be. So you're back to having a single store for money in/out and a separate store for meta-data about a refund.
Obviously there are other fields, as you say -ve values for refunds
As it happens this is nearer a real world paper ledger and separate 'refunds' book.
I've never had to do this , this is just me thinking out loud :-)
给猫剥皮的方法有一百种，但这里有一些“深思熟虑”的要点：
希望这对您有一点帮助，并能指导您获得更好的结构。不要害怕拥有多个表，只要确保您有一个好的方法来关联它们即可！
There are a hundred ways to skin a cat, but here are a few "food for thought" points:
Hopefully this helps a little bit and can guide you to a better structure. Don't be afraid of having multiple tables, just make sure that you have a good method of relating them!