I (will) have a simple table to store the state of a particular flow, containing the following fields (updated via JDBC):
All of these fields are relatively short strings, apart from message which can be upto 120 characters and timeSent is Date/Time.
id : Long
flowName : String
status : String
message : String
timeSent : Date/Time
This table will potentially contain thousands of records, and potential hundred for one flow posted with a different status at different times.
My problem is, I need to keep a record of all state changes to a flow, and need the ability to get the most uptodate state of each flow (called every minute or so), but also see the history (seldom called). As far as I can see, there are three ways to do this, but I don't know which one would be the most efficient.
Method 1: Store the table as above, when I need the most uptodate record; find the record with the max date.
Method 2: Include a "latest" field in the table, every time I add a new record, change the current "latest" record (for that particular flow) to false, and insert a new record with "latest" set to true.
Method 3: Use two tables, one containing all statuses (as above) and another containing flowName and a index to the latest record in the first table.
Which one of these (if any) is the best approach?