2 added 6 characters in body
source | link

2) Add a flag column to your items list say CURR_VER_FLAG and for a given item always pick the record that has CURR_VER_FLAG = 'Y'CURR_VER_FLAG = 'Y'.

  • This flag is set to 'Y' (or true if boolean) on the addition of a new item.
  • When there are any changes to that item you do  

    update ITEM set CURR_VER_FLAG = 'N' where ITEM_ID = ? and
      LIST_ID = ? and CURR_VER_FLAG = 'Y'

    and then insert the new record with CURR_VER_FLAG = 'Y'CURR_VER_FLAG = 'Y'

Your ITEM table now would now be

2) Add a flag column to your items list say CURR_VER_FLAG and for a given item always pick the record that has CURR_VER_FLAG = 'Y'.

  • This flag is set to 'Y' (or true if boolean) on the addition of a new item.
  • When there are any changes to that item you do

    update ITEM set CURR_VER_FLAG = 'N' where ITEM_ID = ? and
        CURR_VER_FLAG = 'Y'

    and then insert the new record with CURR_VER_FLAG = 'Y'

Your ITEM table now would be

2) Add a flag column to your items list say CURR_VER_FLAG and for a given item always pick the record that has CURR_VER_FLAG = 'Y'.

  • This flag is set to 'Y' (or true if boolean) on the addition of a new item.
  • When there are any changes to that item you do  

    update ITEM set CURR_VER_FLAG = 'N' where ITEM_ID = ? and LIST_ID = ? and CURR_VER_FLAG = 'Y'

    and then insert the new record with CURR_VER_FLAG = 'Y'

Your ITEM table would now be

1
source | link

You want to track changes to the items at the item level so your item table would have a key that represents the 'item_id' and something else that distinguishes each state change for that item - you could use a timestamp or a counter or a flag. Let's generically call it 'version attribute'.

Table Design

[LIST]

    LIST_ID
    LIST_NAME
    -- other attributes

[ITEM]

    ITEM_ID
    ITEM_NAME
    LIST_ID -- FK to LIST.LIST_ID
    ACTION_CODE
    -- version attribute (read below)
    -- other attributes

Your LIST to ITEMS relationship would be a one-to-many relationship.

Sample Data [version attribute omitted]

LIST
1    List-1

ITEM        
11   Item-1    1     A -- say added  
12   Item-2    1     A
13   Item-3    1     A
11   Item-1    1     D -- say deleted
13   Item-3    1     C -- say changed

Now there are 2 ways you could track the latest state.

1) Maintain the order of timestamps or counters (if you choose this method) and pick the latest for each item of the list. [Not my preferred way]

2) Add a flag column to your items list say CURR_VER_FLAG and for a given item always pick the record that has CURR_VER_FLAG = 'Y'.

  • This flag is set to 'Y' (or true if boolean) on the addition of a new item.
  • When there are any changes to that item you do

    update ITEM set CURR_VER_FLAG = 'N' where ITEM_ID = ? and
       CURR_VER_FLAG = 'Y'

    and then insert the new record with CURR_VER_FLAG = 'Y'

Your ITEM table now would be

ITEM          
    11   Item-1    1     A   **N** 
    12   Item-2    1     A   **Y**
    13   Item-3    1     A   **N**
    11   Item-1    1     D   **Y**
    13   Item-3    1     C   **Y**-- say changed

I haven't shown it but it should be clear that item_id can no longer be a primary key of this table. You would have to add another distinguishing column probably a surrogate key.

Now you can get

  • all actions performed on a list
  • state changes for any item of a list
  • final state of a list

Note I haven't considered performance here but with right indexes that shouldn't be an issue.