Hi,
I'm searching for some tips on using the Referential Integrity. I use it
almost always, but I'm not so sure if it is the best deal. I think about
cases in which the user must update (or must believe it has updated) the
referenced data without changing the referencing one. I'll try to explain it
with a sample:

Suppose we need to store invoice lines and that each line should contain a
ProductCode, ProductDescription, ProductQuantity and ProductPrice. We could
define an InvoiceLines Table with all this columns and another Products table
containing Code, Description Price of Products. This table would serve als
support for filling new InvoiceLines and could be updated without
interferring with the existing InvoiceLines. So, all without Referential
Integrity.

Suppose now that together with the product description and price, we have a
big amount of other informations that are specific for each product that need
to remain somehow attached with the InvoiceLine. Copying all these in the
InvoiceLines table would do the trick, but since the product details are many
and almost static we end with a big wasted space, containing mostly redundant
data. We could now model this using a Referential Integrity: substituting the
ProductCode and ProductDescription in the InvoiceLines with a ProductID
referencing the PK of the Products Table.

At this point we cannot let anyone change some properties of a referenced
Product, because It would be reflected to previous stored Invoices. Now we
should assure that the referenced Product will never change. If we need some
changes, a new Product will be created with the same data of the superseded
one. The superseded will be marked als not referenceable by new InvoiceLines
(this one probably would be only forced by the application) and the new
Product can be changed before any reference will take place. After that this
Product too is to be considered immutable.

I have similar situations spread over my application and I would use a
standard way to solve them.
I would delegate this on the DB (I'm using MS SQL2005) making this as
automatic as possible, maybe using triggers, but again, maybe you have  a
better solution

Could you point me on the right direction? Any Blogs / Tutorials / Books ?

Many thanks

Jonni