Database design for a limited historical data per user

Question

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.

Answer 1

Plan A: Don't enforce the "limit of 5" when inserting. We can probably handle a billion rows without stressing performance or disk space.

Plan B: As you insert a new timer for a user, delete the oldest of 6 (if there are 6) for that user. That limits the table size to 50K rows -- a trivial table size.

I assume the "10" you mention is not enforced? If it is, perhaps the answer is to store 10 instead of 5. That allows for enforcing the two limits (in different ways).

This might suffice for removing the 6th oldest "timer" for a given user

DELETE FROM tbl
    WHERE user_id = ?
    ORDER BY end_time
    LIMIT 1 OFFSET 5;

Can a user have two timers with the same end_time? If not, then I recommend

PRIMARY KEY(user_id, end_time)

That will make some of the operations efficient.

You might also need

INDEX(end_time)

to handle other actions that you hinted at.

Answer 2

If you are absolutely sure about the 5x2 timestamp limit then you can set up a wide table with user_id and 5x2 timestamp columns, user_id becomes unique in the table and you update entire rows application-side. This would also allow you to move to NoSQL approaches indexed by user.

Migrating this data should be pretty trivial however, and you could generate the above schema with a short SQL view (to be materialised during downtime) so I would recommend sticking with the general-purpose table and migrating when you have an actual performance issue. At this time you will have a better understanding of performance-critical use cases/pain points so your testing and optimisation will be better. And who knows, you might find the historical logs useful in unanticipated ways.