Let us say I have got an internet application that's an outlet of some type. I've got a table that holds all the financial transactions.

custid, orderid,amount compensated...etc

Which table has been employed for sales reviews and whoever else.

Now you want to provide a customer a credit of some type, whether gift certificate or perhaps a refund.

could it be an awful idea to simply come in within the same table having a -amount? Or is it more beneficial to place these in another table?

It is possible to major flaw in setting the website track of this table structure to start with?

I have setup a couple of systems such as this but haven't had much feedback from others, how can everyone usually setup your tables for store like db's.

thanks, Ken

Typically there will be a reason you'd provide a refund, so already the schema with this use situation differs from what purchase.

Now your decision is in the event you keep refund both in places? It makes me uncomfortable getting multiple causes of the reality.

You will have to select how you will exercise the general balance of the customer, storing the in/in multiple places will make this harder than it ought to be. So you are to getting just one store for the money in/out along with a separate store for meta-data in regards to a refund.

Purchase
--------
PurchaseId
ItemId
CustomerId
Payment

Refund
------
PurchaseId
Reason

Clearly you will find other fields, while you say -ve values for refunds

In fact this really is nearer a real life paper ledger and separate 'refunds' book.

I have never needed to do that , case me thinking aloud :-)

You will find 100 methods to skin the cat, but listed here are a couple of "food for thought" points:

  • You may choose to give a "Refund" column that will have a "1" if it's reimbursement, or perhaps a "" for any purchase. After this you need to decide whether or not to keep your amounts all as positive values (and merely take away if there's a "1" within the refund column) or if you would like the comes down to be good and bad and merely consider the refund column weight loss of the indicator (possibly for confirming reasons)
  • You should look at your purchaseID! You think it much more of a "transaction ID" or perhaps an "order number". It might appear like there's no difference in the beginning, but a transaction ID might have a distinctive ID for each entry that will mean an order could be 0000, and also the refund could be 0001 (for exmaple). Should you address it being an order number, the acquisition could be 0000 And also the return would be also 0000 to ensure that you realize the refund relates to that purpose.
  • Growing on my small previous point, I recommend thinking about another Refund table that will have a unique RefundID, CustomerID, OriginalPurchaseID, ItemID, Amount, and Reason column (and possibly PaymentMethod). Profits table would remain virtually exactly the same: (unique) PurchaseID, CustomerID, ItemID, Amount, PaymentMethod. Also, be cautious together with your ItemID because the current setup would need a separate entry (with repeated purchaseID) for every itemID.

Hopefully this can help a bit and will help you to a much better structure. You shouldn't be scared of getting multiple tables, just make certain that you've a good approach to relevant them!