replacement receipts

[DowningDevelopments]

hello,

I have a table called Receipts which holds information on payments made and
stores an autonumber ReceiptNo as the primary key. The problem is to do with
cancelled receipts and their replacements. I already have a tick box which
tells if a receipt has been cancelled.
What i need to have is a way to associate replacement receipts with their
original. I setup a table called PaymentChange which was supposed to store
the OriginalReceiptNo, the ReplacementReceiptNo as well as a
IntermediateReceiptNo which is used to give a -ve amount of the original
receipt and thus show a true customers total (I know the database doesnt need
it but its a business requirement).
Thats 3 receiptNo's which point to Receipts table, this sounds a little bit
weird to me and im wondering if im doing the right thing or if there is
another solution to show which (if any) replacement receipt is associated
with a particular receipt.

with much thanks

Amit

 

[tfossum]

Amit,

I'm not exactly sure what you mean by "...IntermediateReceiptNo which is
used to give a -ve amount of the original receipt and thus show a true
customers total". I don't understand what "-ve" means so understand that as
you read my suggestion.

I would be inclined not to use an additional table. Instead I would be
inclined to add a column to the existing table of the nature
"Parent_Receipt_Number". You are in essence creating a recursive relationship
to the same table, establishing that a receipt can be a parent of another
receipt.

If you are saying a receipt can be cancelled and the replacement receipt can
contatin a new total, and you want to be able to track that total I would
suggest you calculate the difference between the two receipts on the fly.
(Philosophically I believe storing calculated values violates one of those
relational database design maxims (forms).)

 

[DowningDevelopments]

thanks tfossum,

we'd come up with the same solution and have added more columns to the
table, but its good to know that we are on the right lines about it.

Amit