How to store status of rows in MySQL according formalization rules?

Question

I have a table order_executors:

id | order_id | user_id | status

Where order_id and user_id are external keys.

The status columns means the state if row. It accepts 0, 1, 2:

0 - default
1 - accepted
2 - canceled

So, I discoverated the violation of consistancy when:

id | order_id | user_id | status
1    1          1         1
2    1          1         2

In case described above I see that first user accepted a row status = 1 then canceled this status = 2.

So, when business logic retrieves a data by query: SELECT * from order_executors WHERE order_id = 1 AND status = 1 it is still work, despite user has canceled this order.

I can solve this using SELECT * from order_executors WHERE order_id = 1 AND status = 1 AND status !== 2.

Exactly, I can use UPDATE instead INSERT to store the current state of status. But in this case I lost history of order_executors.status.

How to solve this design issue?

My idea is to create a new table order_executors_history where to store status changes:

id | user_id | order_id | status

And inside order_executors store the current state using UPDATE command.

Answer 1

You already gave the solution.

Just update the status (or delete and insert the new row) on table order_executors, keeping only one row per order_id | user_id keys;

Create another table order_executors_history and when you insert/update on table order_executors you should make an insert into order_executors_history.

Both tables having the same structure. You will have the status already ordered by your id inside table order_executors_history.