Database design for actions on some entity

Question

I am making a todo list kind of a service. One todo list will have several items , that can be done/deleted/snoozed etc. Users can also add a new item to the list , delete an item etc.

I want to know what the best practice for this kind of a table should be. say I have 3 actions that can be performed like add/delete/done. I was thinking of storing all the actions that can be performed on that list individually. But this brings the problem of getting the final state of the list. How should I store activity performed on some entity as well as be able to get the final state of it.

It is possible to get all the actions ever performed on the list, and then process them , to get to the final result, but this sounds too processor intensive. Is there a better way , or a best practice that I may follow ?

Answer 1

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 LIST_ID = ? and CURR_VER_FLAG = 'Y'

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

Your ITEM table would now 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.