How to update related table MYSQL?-CodePudding

There are two tables: orders, orders_history.

orders
________
id | status

orders_history
id | order_id | status | user_id

The orders_history contains history of all user's actions. At the same time orders.status contains the last status from orders_history.status.

I make these queries in transation:

transaction start
insert into orders_history...
$status = select status from order_history order by id desc limit 1;
update orders set status = $status where orders.id = id

My question is:

Should I use transaction and is it properly way to do that? What if several transactions try to insert, update orders_history for the same order_id.

CodePudding user response：

As suggested in the comments above you could use a trigger to update the orders table -

DELIMITER $$
CREATE TRIGGER `update_order_status` AFTER INSERT ON `orders_history`
  FOR EACH ROW
    UPDATE `orders` SET `status` = NEW.status WHERE id = NEW.order_id;
$$

DELIMITER ;

The better option would be to not store the redundant status in orders and just query for most recent status in orders_history.

SELECT orders.id, (SELECT status FROM orders_history oh WHERE orders.id = oh.order_id ORDER BY id DESC LIMIT 1) AS status
FROM orders

CodePudding user response：

The design pattern I might use in this case is...

Table 1: History -- this is an audit trail of everything that has gone on. (Think: All the checks written and deposits made to a checking account.)

Table 2: Current -- this is the current status of the information. (Think, current account balance, status, etc.)

Whenever something happens (eg, a check clears):

START TRANSACTION;
INSERT INTO History ...;
UPDATE Current ...;
COMMIT;

In the case of a checking account, something different is needed if your account is overdrawn, so let's make the transaction more complex:

START TRANSACTION;
SELECT balance FROM Current WHERE acct = 123   FOR UPDATE;
if would be overdrawn then 
    email user
    UPDATE Current SET status = 'overdrawn' acct = 123;
    ...
else
    INSERT INTO History ...;
    UPDATE Current ...;
endif
COMMIT;

I prefer to put the "business logic" clearly in one place, not hidden in a Trigger. (I might use a Trigger for monitoring or logging, but not for the main purpose of the tables.)