CODEWITHSUNDEEP
database-design
gbro3n
gbro3n

Reputation: 6967

Database Design Best Practice - Order processing database - Where updates to products could effectively change a closed invoice

just after some insight on best practice here.

I'm building an order processing database, which manages stock, orders and invoices.

In my design so far, the invoices and orders have foreign key references to a products table. If the products were to be updated after the creation of an order / invoice, then the list of items on the rder / invoice could change if it were viewed again.

I'm planning to manage this by creating an "OrderItems" table, which stores an immutable copy of the product data as it was at the point where the order / invoice was created, and have the order reference this table rather than the changeable product table.

Is this a common method of handling this problem - is there another approach?

Thanks

Upvotes: 6

Views: 2046

Answers (2)

user212102
user212102

Reputation: 81

Need to make a clear distinction between the price of a product, perhaps its default-price (in the Product table) and the price it was actually sold at (held in the InvoiceLine table ).

Upvotes: 2

S.Lott
S.Lott

Reputation: 391872

This is related to the "Slowly Changing Dimension" problem in data warehouses. There are a half-dozen standard solutions.

http://en.wikipedia.org/wiki/Slowly_changing_dimension

Your "freeze a copy" is like the Type 4 algorithm.

Upvotes: 10

Related Questions