one to many relationship

[Neruda]

Hi,
say you have a tblProducts table with the usual list of items and prices and create an invoice that contains a list of products (pkProducts in another table with other releshionships)and a year later decide to totally change the name of a product, that invoice is not correct anymore, if I print it will show the new product and not the original one.
Having a duh moment

Thanks

[cactusdata]

Simple: Save the current product name and price as fields of the invoice detail (line) record.

[twinnyfo]

I should point out that, in general, such advice to save duplicate data as such is a drastic "no-no" in DB design.

However, in this case, this is the only way to save historical product names and prices. The only other possible way to maintain the historical record is to add a date field in the products table and have multiple entries for each product, listing the date with the current product name and price. However, that level of insanity and the complexity of working through invoices would be enough to give anyone a headache. It is simpler to do as CactusData has described.

Additionally, in your case, if the item has changed and not just the name of the product, I would recommend adding a new item to your Products Table. However, if you have a rotating list of products available, this might not be necessary.

[Neruda]

Great thanks, I just did not dare duplicating data!
Interesting to add a date field in products, as you said a bit OTT for what I am doing but excellent answers
Thanks a lot!