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
Avoid Redundant Data The table in the database should be constructed following standards and with utmost dedication. It should have different fields and minimize redundant data. The table should always have a Primary Key that would be a unique id.
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.
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 ).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With