1: What is the best way to store data of a product in invoice? Is it by bringing the id of the product, or by bringing all info related to the product? Actually is it a good practice to store id of the product in the invoice not all the product data (as I heard before), especially in this situation?
Storing only the ID of the product in the invoice table would be the correct way to relate the two.
2: What is the best way to delete the product in this situation? Is it for example, by creating deleted or delete_status column and assign 0 if the product is not deleted and 1 if it is deleted, and display all products in the invoice page regardless of delete status (and select products that are not deleted in the products page). Or, just by adding product info to the invoice not the product id, so this way no need to join tables together.
DELETED column, and never hard delete the row from the database. This way all existing invoices can still reference a product that was sold. The only time you might want to really delete a row is if the product never really existed and was created by mistake.
3: Same thing for update, if I change product name, it will change also on an already created invoice with the same product. What is the best way to update the product info and prevent this from happeneing?
You should only be changing the product name if it is truly incorrect. Otherwise, you're technically creating new product. If the name was incorrect, such as typo, you would want the update to reflect on all previous invoices. For example, You would not create a product called “Mountain Bike” and then rename it later to “Skateboard”. Those would be different products entirely. In that case, you’d want to create a new product. However, if you created “mountain bake” by mistake, you would want to update it to “mountain bike”, since that just fixing a typo.
As for a FK, you will want to considering creating one from Invoice.ProductID to Product.ID. This serves two purposes.
- It prevents an invoice from being created with a line item for a product that does not exist
- It prevents a product from being deleted if it’s referenced on an Invoice.
Lastly, you probably need a third table, InvoiceLineItem.
In that scenario, Invoice would contain order header type info, such as customer, shipping address, etc. InvoiceLineItem would contain reference to each product ordered, qty, and map back to Invoice and Product.