Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2009
    Posts
    2

    Question Most efficient way to store history

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    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.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2009
    Posts
    2
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •