Answer the question
In order to leave comments, you need to log in
What is the best way to store a timestamp in the database for each action?
Let me simplify a bit to make it clearer.
There are 2 buttons A and
B. It is necessary to store (in the database) the time the buttons were pressed (up to seconds) and which button was pressed, how best to do this?
If you store everything in epoch in one line (example: "AD549795d1BD549795d3BD549795d6"), then the line can potentially grow up to 15Kb (even if it's time to translate in hex), it will be requested often, 100-300 (at the peak) users, every time in 10 seconds (540-1620 MB/hour).
Store the time of the first click in another row in the database, and then add the difference to the row?
Store in a separate table separately with TIMESTAMP for each click?
Of course the goal: less load on the server, less traffic
ADDED:
Sorry for the inaccurate question.
There are many operators (who press buttons). Here is the most suitable analogy: A
football match and an operator who keeps statistics (who gave a pass to whom, shots on target (buttons-events), etc.) There are many operators, each operator has many matches, n to n. One match has only one operator. 1 to 1. You need to store the match, the event and the time of this event in the match.
ps When you do this all the time, everything is logical and simple, but when, 7 years after the university, you try to remember something in your head, it's a mess. Especially when, having asked a question, people, before giving an answer, first elevate themselves / belittle the interlocutor, and then answer the question. There are doubts about the professionalism of these comrades and you always treat their answers with suspicion. I have never met professionals in my life who would answer questions in such a vein. Hope I didn't offend anyone.
Answer the question
In order to leave comments, you need to log in
The answer received on the English service after 10 minutes:
I suggest you create reference tables called
match match_id, name, venue A row for each distinct match
player player_id, name A row for each distinct player
action action_id, name This is a codelist 1=Ace 2 = Fault, etc.
These tables will be relatively static.
Then, I suggest you create an event table containing the following items in the following order.
match_id
ts (TIMESTAMP)
action_id
player_id
You should include all four of these columns in a composite primary key, in the order I have shown them.
Every time your scorers record an action you'll insert a new row to this table.
When you want to display the actions for a particular match, you can do this:
SELECT event.ts,
action.name AS action,
player.name AS player
FROM event
JOIN player ON event.player_id = player.player_id
JOIN action ON event. action_id = action.action_id
WHERE event.match_id = <>
ORDER BY event.match_id, event.ts
Because of the order of columns in the composite primary key on the event table, this kind of query will be very efficient even when you're inserting lots of new rows to that table.
MySQL is made for this kind of application. Still, when your site begins to receive tons of user traffic, you should probably arrange to run these queries just once every few seconds, cache the results, and use the cached results to send information to your users.
If you want to retrieve the match IDs for all the matches presently active (that is, with an event within the last ten minutes) you can do this.
SELECT DISTINCT match.id, match.name, match.venue
FROM event
JOIN match on event.match_id = match.match_id
WHERE event.ts >= NOW() - INTERVAL 10 MINUTE
If you need to do this sort of query a lot, I suggest you create an extra index on (ts, match_id).
Ollie Jones
It is necessary to store (in the database) the time of pressing the buttons (up to seconds) and which button was pressed, how best to do this?
Store a string with a time stamp for each click, send requests to the server immediately upon clicking, take the time from the server in the database (default CURRENT_TIMESTAMP), and not send it from a client that does not know how the clock is set.
Depends on the task.
You can store: start+offset1+offset2+...+offsetX
You can store: count, average time and last click time.
Can be stored: in cookies on the user's side and transferred in batches.
there are more options...
Describe the problem in more detail..
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question