Hello!
We have 2 tables in our database.
- PROCESS (ID as key, PHASE, [...])
- PROCESS_PHASE (ID as key, PHASE as key, [...])
The first one saves 1 entry per process.
The second one saves 1 entry per process phase.
The way the code works it's like this (code in bold are interactions with the database):
- Process gets created.
- Process is saved in PROCESS.
- Process phase is saved in PHASE_PROCESS.
- Process gets updated.
- Code calls the corresponding function that updates the phase.
- Gets the process from PROCESS using it's ID (to check if exists and if the phase is different).
- PROCESS gets updated with the new data (like the phase).
- The last phase in PROCESS_PHASE gets marked as ended.
- New entry in PROCESS_PHASE it's created with the new phase.
- Process gets updated (if it is not the last phase go to step 5)
- Gets the process from PROCESS using it's ID (to check if exists).
- The process gets marked as ended in PROCESS.
- The process gets marked as ended in PROCESS_PHASE.
- The flow of this process is now ended (no more phases).
As you may see, updating the phase of a process "costs" 4 calls to the database (6, 7, 8 & 9). I don't really have a lot of knowledge on databases but maybe there is another way of doing this more efficiently.
Note: the update of the process could be asynchronous.
I'm looking for ideas or tips on how to improve it.
Thanks in advance!