Best approach to handle user statuses and keep their history

Question

I am developing a Spring application which has a few different modules. There will be a bunch of users added in the database.

I want to add a feature, which will allow me to track users' availability, which will be set by themselves. It will be a simple toggle between available and unavailable.

• Each user must have a current status set in DB
• A history of status changes should be available for each user

My goal is to have a possibility to see current status of every user + to have a historical data. I need a history to create some statistics of availability, for example to check what was John Doe's availability during last week, month etc.

My idea is to create two tables, for example user_status and user_status_history. Once user changes his status, an entry from user_status table is copied to the user_status_history table, then the status and the timestamp in user_status are changed afterwards.

Based on the timestamps I could calculate for how long user was available/unavailable. I am wondering if history data table is enough or if I should add there some more columns like timestamp_end or duration.

What will be the best solution for that problem? I am asking for best approach in regards to database and the app itself.

Answer 1

Normally, for full historical tracking, you don't need the current status in a different table.

Instead, add two timestamps to your table which represent the start of the given time slice, and the end of the given timeslice. For an update, just set the end timestamp and insert a new row.

Something like this:

userid | status    | validFrom           | validBefore
-------+-----------+---------------------+---------------------
joe    | available | 2020-01-01 01:12:12 | null

OK, joe is available. He is still available, as "validBefore" is null.

Current status is always where validBefore is null.

Change that an hour later:

update status_table set validBefore = <now> where user = 'joe' and validBefore is null

insert into ...

userid | status    | validFrom           | validBefore
-------+-----------+---------------------+---------------------
joe    | available | 2020-01-01 01:12:12 | 2020-01-01 02:22:00
joe    | n/a       | 2020-01-01 02:22:00 | null

Back online at Jan 02:

userid | status    | validFrom           | validBefore
-------+-----------+---------------------+---------------------
joe    | available | 2020-01-01 01:12:12 | 2020-01-01 02:22:00
joe    | n/a       | 2020-01-01 02:22:00 | 2020-01-02 12:20:21
joe    | available | 2020-01-02 12:20:21 | null

Another alternative is replacing the null with a timestamp far in the future (2999-12-31 or so.) That way, you can always use a BETWEEN query to find out joe's status at a given time.