I'm developing a PHP/MySql retail shop management solution (for the first time!) that keeps records of sales and inventory. I have a problem, which will be better understood by the following chronology of events:
2021-01-19: Shopkeer_Joe purchases 2 chairs from supplier_Tom and enters the record into the software which creates produc_id '1' in the database.
2021-01-20: Shopkeeper_Joe sells both chairs to customer_Alicia, with sale_id, '1' referencing product_id '1' in database.
2021-11-20: (10 months later) With zero chairs sold since customer_Alicia bought the last lot, Shopkeeper_Joe decides to order Desks this time (not chairs) from the same supplier_Tom, But this time, instead of inserting a new record for newly supplied Desks, shopkeeper_Joe, for whatever reasons edits the zero-quantity old chair product entry (product_id '1'), and simply renames the product name from Chair to Desk, thus creating a problem now my software is showing that on 20th-January-2021, Alicia bought two Desks when indeed, she bought two chairs!
My question is how can I ensure that the sale records, in my software stay truthful ??
UPDATE:
I don't want to ban users from editing records because they genuinely may have to correct some mistake in their product record. I am actually trying to research if there is any way, I could keep the sale records truthful while keeping the product edit option open. Maybe by keeping a history of records, like 'temporal database', like querying to get 'what was the name of the product on sale date?' and then displaying 'Original Sale' (as issued on sale date), and 'Modified/edited Sale' because of foreign-key referenced product renaming. BUT I don't know much (anything?) about temporal databases, any help/clue would be appreciated.
Best Answer
This problem is essentially that of slowly changing dimensions. Typically you would not allow editing such records in place; instead you create a new version of it (if your user is stupid enough not to realize that a desk is a different product), while the old sales record still references the previous version.