How to deal with invoices that reference products that don't exist any more?



  • I am developing an ERP using PHP and MYSQL. I have a product page and an invoice page. I am facing an issue, if I delete a product from products page, and I already created an invoice with this product, the invoice will be deleted, and I don't want that to happen. I want that the invoice will stay regardless if the product inside the invoice is deleted or not. Maybe this is happening because I am storing the id of the product in the invoice table (and joining table when select) not the product data itself. Now, I have 3 questions regarding this:

    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 (as I heard before) not all the product data, especially in this situation?

    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.

    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?

    One last thing, does foreign key constraints will help me for these situations? Because I tried it once, and I think it makes things worse.



  • 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.

    Use a 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.

    1. It prevents an invoice from being created with a line item for a product that does not exist
    2. 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2