0

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.

9
  • 1
    Is shopkeeper Joe a DBA? I think not. – Walter Mitty Mar 14 at 3:31
  • 1
    Don't grant rights to individual people to write to table. Only the app is grated that, and it has no ality to change product names. – Michael Green Mar 14 at 10:47
  • @WalterMitty, Yes, shopkeeper Joe is not DBA, but because he is using my software, I feel kind of responsible for implementing my software, in a way, that does not let user falsify the Sale record. – FAQi Mar 14 at 14:34
  • 1
    @WalterMitty Correct me if I'm wrong, but this looks like it's intended for small retailers, which means that Shopkeeper_joe is the DBA, whether he realizes it or not. – Darwin von Corax Mar 14 at 20:52
  • 1
    I think you are right. So. It sounds like the real problem is protecting the data from Joe's mistakes. A tall order. – Walter Mitty Mar 15 at 11:31
0

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.

2
  • wouldn't it be kind of bad UX for my software users, to prevent them the ability to edit the product entries? What if they genuinely have to correct some mistake in their product record? I was actually trying to research into if there is any way, I could keep the sale records truthful while keeping the product edit option open. Like user could edit the product, yet the sale record stays truthful, by keeping a history of records, like 'temporal database'. i dont know much about temporal databases, like querying to get 'what was the name of the product on sale date? – FAQi Mar 13 at 20:06
  • What it looks like to the user is up to you; I have described what happens in the database, we are on the DBA site after all. – mustaccio Mar 13 at 20:32
0

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.

1
  • Can you elaborate a bit on how may I implement this product_history table, and then structure the query to get 'Original Sale record' by querying what was the name of the product on sale date? – FAQi Mar 14 at 14:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.