Let me explain; I've got two tables:
# tb_buy
buy_id
address_id
buy_value
# tb_buy_items
product_id - foreign key of a product table
buy_id
It's only an example, but is it recommended to use a structure like this?
Or should I put info about the product in tb_buy_items? Like name, cod, value, because if someone deletes this product, the line will be removed or it will set the foreign key to NULL and no-one will know that product was purchased...?
I would like some hints about this, thanks.
if ¨buy¨ table is for historical purpose (to hold the order history) I would just make a copy of all information that I would like to keep (code, name, price at that the purchased moment etc). just as you said, a change of product will affect all. but if it´s for ¨cart¨ table that should have most updated information, I will have the foreign key