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 ?
Best Answer
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'.
Your
LIST
toITEMS
relationship would be a one-to-many relationship.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 hasCURR_VER_FLAG = 'Y'
.When there are any changes to that item you do
and then insert the new record with
CURR_VER_FLAG = 'Y'
Your ITEM table would now be
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
Note I haven't considered performance here but with right indexes that shouldn't be an issue.