How to implement 'temporal database' to keep Sales records truthful in POS?

Question

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.

Answer 1

My first thought is that this is a user discipline problem. My second thought is that you could add a history table to the database. Then create a before row trigger on the products table which says to insert a copy of the old product record, along with a timestamp and current userid, into the product_history table. This would not prevent Shopkeeper_joe from changing chairs into tables, but would record that he did it and when.

Suppose your products table is defined as

CREATE TABLE products
(
  id        INTEGER       AUTO_INCREMENT PRIMARY KEY,
  name      VARCHAR(50),
  desc      VARCHAR(255),
  unit_cost DECIMAL(7, 2)
  -- other fields of interest
);

You would then define product_history something like

CREATE TABLE product_history
(
  id        INTEGER,
  name      VARCHAR(50),
  desc      VARCHAR(255),
  unit_cost DECIMAL(7, 2),
  mod_date  DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  mod_by    VARCHAR(32)    NOT NULL DEFAULT CURRENT_USER,
  PRIMARY KEY (mod_date, id)
);

You then define a trigger on products, something like

CREATE TRIGGER update_product
  BEFORE UPDATE OR DELETE ON products
  FOR EACH ROW
  INSERT INTO product_history (id, name, desc, unit_cost)
    VALUES (OLD.id, OLD.name, OLD.desc, OLD.unit_cost);

Note that we use the default values for mod_date and mod_by.

Your query would then be something like

SELECT id, name, desc, unit_cost, mod_date, mod_by AS 'Who to blame'
  FROM product_history
  WHERE DATE(mod_date) = date_of_interest
UNION
SELECT id, name, desc, unit_cost, mod_date, mod_by AS 'Who to blame'
  FROM product_history
  WHERE DATE(mod_date) > date_of_interest
  LIMIT 1

to retrieve the old values as of the first modification after the date of interest and all modifications (if any) on the date of interest. (NOTE: I haven't tested the syntax; I leave that as an exercise for the reader.)

Finally, code your application to display a big scary warning if anyone attempts to update the product name or description, since they really shouldn't be doing that anyway.

I hope that helps.

Answer 2

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.