I'm working with 5 processes that take about 8 hours to run, during each 8 hour process, it changes its location at random times but in a specific order. There could also be all 5 processes running at the same time.
I'm trying to track when the process starts, when it changes locations and when it stops, easy right.
I have gone back and forth trying to decide on a solid scalable design, i came up with the two listed below, I wanted to get everyones thoughts on why or why not either should be used. Any tips or suggestions are greatly welcomed
** The table consists of five columns: id, location, operation, started, finished.
- when the process starts, id, location, operation and started are inserted into DB, finished is left NULL. When the process goes to the next location, the table is queried for the last operation at the location with a NULL finished time. Then the finished time is updated and then the next location is inserted into the database.
** I don't like this design but its quick and easy, the only problem i see is if somehow a row does not get closed or finished and there are two operations with the same location with Null finished times.
- Same as number one except for when a row is inserted, i get the id of the last inserted row and save it in a variable, then once the process has moved locations i can simply update the row per its id.
** I like this design but since the process can run for 8+ hours, if the program crashes or if there is a pc issue i could lose the id and the script will fail.
- i thought about breaking out the start and finished into their own separate tables but i am not in favor of this design unless someone can convince me otherwise.
Thanks for the help and suggestions!