I use Python sqlalchemy to store my model in a MySql database. One of my objects has a 'state' field (for simplicity let's assume there are two states: AVAILABLE and UNAVAILABLE).
There are different micro services which can modify the states of the objects - such as the API service, periodic tasks, background tasks, etc. States are updated with UPDATE queries, which means that currently I have no way to get historical states for each object. States can be updated as frequently as multiple times per second, or as infrequently as once a year.
My goal is to track the time an object spends in each state. For example:
- Within the last 24 hours, instance A spent 6.5 hours in AVAILABLE state and 17.5 hours in UNAVAILABLE state
- Within the last 6 months, instance B spent a total of 95.9 days in AVAILABLE state and 84.1 days in UNAVAILABLE state
How would you design an architecture for that? Do I create a separate table in MySql to store historical states? Or perhaps a NoSql database is better for this task? What tables/object structure would you use here? I prefer to rely on open-source or even commercial solutions rather than develop myself.