Database design - 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

 

[ByteMyzer]

The product price should be copied into the invoice record. That way, in the event that the product price changes, the change will have no effect on the invoice records.

This should always be the methodology behind historical transactional data. If you simply rely on a relational link to obtain this data from the Product table, you will not have accurately reflected Sales Price History.

 

[The_Doc_Man]

1. Please be careful about multiple posts of the same question. Because you posted the extra question, you have the right to delete it.

2. The question about invoiced cost vs. cost in product table comes about because of normalization. Referential Integrity merely exposes the problem.

Your problem is that you have a field in the table that depends on data not in the table and not linked to the table. This is a normalization violation. All data in any table must depend on data in that table as its prime key. If prices change with dates, then the price X depends on the date, which is not part of your price table.

Your unit price table should have a date in it to show when the date was valid. (And to make it easier on yourself, add a date to show when the price was changed, keep in the same price record.) THEN make a copy of the entry for which the new entry's price is valid on the date of the price change.

You can copy obsolete prices to a price history table with an appropriate series of queries if you wish, then delete them from your current product table.