I need to store data related to timer sessions. My MySQL table looks like this:
user_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY timer_start_time TIMESTAMP timer_end_time TIMESTAMP
There are many users (>10 000) that do several (<10) timer sessions every day. However, I need to store only the 5 most recent ones. So there can only be 5 records per user. How do I design a schema that adheres to these requirements and remains performant?
I'm considering storing JSON objects in VARCHAR, but have heard that it's a bad practice.
I would prefer to use MySQL for this, but I would consider switching to any other database given the performance gains.