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.

 

[Pat Hartman]

Referential integrity ensures that each foreign key has a matching primary key value in the parent table. For example. Customer-->Order. The CustomerID in the Order table MUST exist in the Customer table or the Order cannot be added.

Cascade delete is not used on every relationship. It is used ONLY when you want to delete all child records if you delete the parent. An example would be Order-->OrderDetails. When you delete the order, you also want to delete the items that were ordered. Cascade delete is not normally selected for the Customer-->Order relationship because you wouldn't want to accidentally delete an open order or even delete order history. So usually orders are deleted or archived periodically to keep the table size manageable. If there are no orders, then Access will allow the Customer to be deleted.

Cascade update does not cascade data fields. It ONLY cascades primary key changes to the child table's foreign keys. For example, if you changed a customer's CustomerID, you would want to change the CustomerID in all of that customer's orders. That's what cascade update is used for. If you use autonumbers as primary keys, they can't change so cascade update is never necessary.