creating a historical database, need help making a decision 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!
Your first design is the way to go.  Keep everything about a process on a single record.  Your challenge isn't one of database design, it's one of restartability/disaster recovery.

Since your processes are long-running, you have a higher risk of the data getting out of sync with the processes that are being tracked in the data.  For example, you lose power causing a process to crash.

The solution is that you need to change your processes so that when they start they confirm what the data says.  Does the database say that process X is still unfinished?  Is process X still running?  If not, your data is out of sync and you need to mark the record for process X as failed.

You might want to consider adding a couple of columns to your first table design, for example:


last_update - the date/time when the record was last updated.  Update this periodically during your long-running process.
status - keep a code to indicate "Active", "Complete", "Failed"