Need optimized sql advice

Question

So i have this database full of tables like suppliers, clients, stores, store_users, services(of each store), repairs, etc etc (a database of a IT brand that repairs computers at each store).

In the "repairs" table i have fields regarding the client, hardware, breakdown, condition and state of the repair("started", "waiting for client answer", "delivered" and others).

Everytime there are changes in the repair, for example: user1 received the repair order and inserted the repair into the system. user2 tested harddisk, RAM, etc for problems, found major disk problem so it needs to be replaced.

This information will change many fields in the "repair" but i need something like another table "interventions" to know what changed and who did what in each time there was an edit to the repair.

The only idea i had was to make a new table "interventions" with all the fields from the "repairs" table and every time someone edited the repair it would copy it to the intervention with a id_repair and a id_user linked.

PS: Users are the store's employees

Answer 1

It sounds like your "repair" table needs to be renamed to "interventions" and what you currently see as the "repair" table changed to a view which shows the most recent intervention.