[chylld]

Hi All,

I'm currently designing a new database for our company that needs to keep track of payments and thus receipts. Each receipt has associated with it a number of payments (e.g. cash, cc, cheque) as well as a number of items for which the payments are being made.

The complication I've hit arises from one of our business requirements which states that the contents of the receipt must never change, i.e. if the receipt says product A $5 discount B -$2, it should still say that even if either item A or B changes price.

I've thought of two possible solutions to this problem:

1. Store all product name/price/whatever changes as transactions, and have the database look up the appropriate details according to the time of the transaction
2. Create hard copy receipt_item entries for each receipt that won't have their name/price change ever

I'm not particularly happy with either of these solutions; can anyone suggest a better method?

Thanks in advance,
Jonathan.

[blindman]

For this particular situation, go with option 2. Store the values with the receipt as a snapshot of the item values at the time of the transaction. This does not violate the principles of normalization.

This does not mean you might not also want to incorporate option 1, if you require a detailed history of changes to the values.

[certus]

Option one is actually favorable for a design that will have a longer lifespan.

[blindman]

The only way to be sure of the values at the time of the transaction is to store those values within the transaction. At that point, the values become logically separated from the original item values, and relational integrity is no longer required (or desirable).

[chylld]

thank you both for your replies.

I have thought about the problem considerably since my first post, and another business requirement just hit me in the face.

there are often situations where a customer will purchase products A and B, and get a discount C because of the combined purchase. however, sometimes the customer decides to return product B, and our policy is to recalculate the discount (if any) and refund them based on how much they originally paid, and the new total price.

for this scenario, we use the price of each product at the time of the original purchase, and i would feel much more comfortable if the original price were to be stored with the course along with a timestamp (i.e. a history table) rather than only with the receipt.

[chylld]

side question: i've done some reading on triggers and understand how it can help with keeping track of when product details are updated by automatically making a history table entry. while this seems all good, is there any easy way to do the reverse, i.e. query the product details along with a timestamp, and get the product details as they were at that time?

the easiest way i can think of to do this is to match each product with its most recent (up to the given timestamp, or by default, now()) entry in the history table...

[blindman]

Yes, that is the way to do it. I often create "AsOf" procedures that will return a status as of a specified date. I keep these separate from the "Current" data procedures, and they access different tables and are more expensive to execute.