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.