Database Structure - Referential integrity

[Fransdekock]

Hi i have a quick question

Lets say u have a database that keeps track of all items sold and their respective prices. So u have a items table that keeps the product information and price. Then these items are displayed on invoice kept in a invoice table linked with a foreign key and a one to many relationship.

My question is lets say the price of an item should change....will the invoice table of old invoices display the new price or old price. Is this where the referential integrity comes to play ? Should u select it when doing the relationship ticking the cascade update related fields and cascade deleted records boxes respectively or not ?

Anyone have any views on this ?

Ur help will be much appreciated

Kind regards
Frans

 

[wazz]

this is a case in which you should store the price in the invoice table, i.e., copy the price from the product table to the invoice table when the invoice is saved so your invoice record remains accurate (does not change) when the price changes.