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_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
What will be the best solution for that problem? I am asking for best approach in regards to database and the app itself.