CODEWITHSUNDEEP
mysqldatabaseprocess-management
Janis Peisenieks
Janis Peisenieks

Reputation: 4998

Disregard changes to a product description when retrieving order records

The title is somewhat hard to understand, so here is the explanation:

I am building a system, that deals with retail transactions. Meaning - purchases. I have a database with products, where each product has an ID, that is also known to the POS system. When a customer makes a purchase, the data is sent to the back-end for parsing, and is saved. Now everything is fine and dandy, until there are changes to the products name, since my client wants to see the name of the product, as it was purchased then.

How do I save this data, while also keeping a nice, normal-formed database?

Solutions I could think of are:

Any thoughts on this?

Upvotes: 0

Views: 52

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

This is called a slowly changing dimension.

Either solution that you mention works. My preference is the second, versioning. I would have a product table that has an effdate and enddate on the record. You can easily find the current record (where enddate is null) or the record at any point in time.

The first method always strikes me as more "quick-and-dirty", but it also works. It just gets cumbersome when you have more fields and more objects you are trying to track. It does, in general though, win on performance.

Upvotes: 1

Robert Harvey
Robert Harvey

Reputation: 180808

If the name has to be the name as it was originally, the easiest, simplest and most reliable way to do that is to save the name of the product in the invoice line item record.

You should still link to the product with a ProductID, of course.

If you want to keep a history of name changes, you can do that in a separate table if you wish:

ProductNameID
ProductID
Date
Description

And store a ProductNameID with the invoice line item.

Upvotes: 0

Related Questions