assuming I have a table similar to the following:
table [task]
Id | status | duedate | solution | datecompleted |
---|---|---|---|---|
1 | inProgress | 1/1/2024 | ABC123 | null |
2 | notStarted | 1/2/2024 | XYZ456 | null |
3 | notStarted | 1/2/2024 | ABC123 | null |
4 | Notstarted | 1/2/2024 | ABC123 | null |
Is there a way in MySQL to do the following:
Given I update the status value to completed on a task > dateCompleted is set to current DT.
Update duedate column for all rows with shared value from solutions column by extending other due dates by the amount of days past duedate that datecompleted is.
but only if the datecompleted is greater than the dueDate
Currently this process is being handled before we write to the data base. this is an attempt to alleviate some of the complexity of our system. I am relatively new to MySQL, if this is against best practices or not something within the functionality of SQL that would also be great to know.
2
Answers
The application logic is that there are two data changes.
The first change is that a single row changes status (from "in progress" to "completed").
The second event, that fires when the first one occurs, is that related rows should have some business logic applied.
These events are related at the domain level, but at the database level represent two different events.
There are 3 approaches, each with benefits and drawbacks.
The first is to embed the logic in the application layer (as you do today).
The application would presumably create a transaction, and then execute the SQL statements to update the initial task, and the related tasks.
The benefits are that the business logic lives in the application, not the database – in general, you want business logic outside the database, because it tends to change more rapidly than database structure.
The second is to use a stored procedure, which executes the SQL statements in a piece of business logic in a database artefact. Reasonable people can disagree about this – but stored procedures are harder to test than application code, and most people want business logic outside the database.
The third option is to use a database trigger – whenever the "status" column changes, you can fire a trigger. Again, reasonable people can disagree – but triggers are even harder to test than stored procedures.
I would recommend having another table to store the status of a solution, so you can change its status, due date, completed date in one place. This is related to the concept of relational database normalization — store each fact in only one place.
But given the table you have, you could update all task rows this way: