How to decrement a field in a different table on deleting an entry in MySQL

Question

I would like to know how to decrement a field (projects) in another table (users) after deleting an entry in a table (projects).

Users table:

+-----------+--------------+------+-----+---------------------+----------------+
| Field     | Type         | Null | Key | Default             | Extra          |
+-----------+--------------+------+-----+---------------------+----------------+
| id        | int(11)      | NO   | PRI | NULL                | auto_increment |
| username  | varchar(50)  | NO   | UNI | NULL                |                |
| password  | varchar(255) | NO   |     | NULL                |                |
| superuser | tinyint(1)   | NO   |     | 0                   |                |
| projects  | int(11)      | YES  |     | NULL                |                |
| date      | date         | NO   |     | current_timestamp() |                |
+-----------+--------------+------+-----+---------------------+----------------+

Projects table:

+---------+--------------+------+-----+---------------------+----------------+
| Field   | Type         | Null | Key | Default             | Extra          |
+---------+--------------+------+-----+---------------------+----------------+
| id      | int(11)      | NO   | PRI | NULL                | auto_increment |
| name    | varchar(50)  | NO   | UNI | NULL                |                |
| creator | varchar(255) | NO   | MUL | NULL                |                |
| visits  | int(11)      | NO   |     | 0                   |                |
| file    | varchar(50)  | NO   |     | NULL                |                |
| date    | date         | NO   |     | current_timestamp() |                |
+---------+--------------+------+-----+---------------------+----------------+

Answer 1

Denormalization in MySQL is not that easy.

Generated stored columns cannot work across tables, and views are not materialized as well.

Flexviews ( https://github.com/greenlion/swanhart-tools ) was warmly recommended in O'Reilly's book, High Performance MySQL 3rd edition, but it is discontinued for newer version of MySQL and would either way mean you had to setup your MySQL server yourself (cannot used managed cloud DBs).

This means you are left with

1. Triggers in MySQL
2. Doing the update from your application code

What you choose depends on your setup, but using triggers should be done with caution. They are easy to overlook and can cause deadlocks/performance hits, that are hard to debug.

Read more about triggers here: https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html