Hi,

I (will) have a simple table to store the state of a particular flow, containing the following fields (updated via JDBC):

id : Long
flowName : String
status : String
message : String
timeSent : Date/Time

All of these fields are relatively short strings, apart from message which can be upto 120 characters and timeSent is 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?

Thanks,
Matt

In this situation a "history table" approach might be better (see type 4 here: http://en.wikipedia.org/wiki/Slowly_changing_dimension). You only keep the most current record in the main table and, whenever it changes, copy the old version to the history table. You can then define a view over the union of the two tables to access the history.

Thank you for the info n_i, it was very useful. I have done a bit of research into SCD desgin patterns, but there dosen't seem to be much information on the effceiency of each type. Purly from design view, type 4 seems to suit my needs the best.

Many Thanks,
Matt