Design issue

[tscotti]

Hello,

I Have a simple database that combines three tables (Style, Fabric, Products) that are linked with referential integrity relationships.

The issue is when I change, for example, a cost value in a Fabric field and it changes all the associated records in Products with that corresponding value. In other words, I do not want the values in previously created records of Products to change when I make a new change in a Fabric field. I want to put a "freeze" on previously created records. Historical values must not change.

Any ideas?

Thanks, in advance, for any help offered.

Tony

 

[Bodisathva]

Unfortunately, if you are going to enforce the referential integrity, this will happen unless you are willing to create new entries for each change or archive off historical data so that it is no longer part of the enforcement.

 

[boblarson]

This is one situation where you would want to store the price for the object in the appropriate table, in addition to the foreign key. And what Bodisathva wrote is correct, you can link your tables within the relationships, but the joins should be set without R.I.