0

I have a table that takes INSERTS to store integers for a date. These integers are then summed for each date with sum(COLUMN) and the total is used. So, date cannot be unique as there are many inserts per date. Integer value itself cannot be unique either.

I use the system to count entries (for instance at a restaurant, club, whatever).

A person holds an iPad at the door and sends an INSERT command for how many people entered (like a group of 5 would be a row with an integer value of 5 and the current date).

If there is a bad connection and the iPad sends the request but doesn't receive an answer, then the user will attempt to perform the insert again, causing duplicates.

Would it be sensible to add a column such as "IDENTIFIER" with a random string/number/hash etc. that would then be unique, so that if the user retries the insert and the server already has the row, it will give the same reply as if the insert succeeded.

I'm having trouble navigating the logic in handling errors such as these. If it were an UPDATE command on a unique column this wouldn't be an issue, but the way I built this that's not really possible.

Improve this question
8
  • Why not just record the actual time the app action was performed? If the application simply doesn't allow more than one action a second/millisecond (e.g.), then a DATETIME/TIMESTAMP with a second/millisecond resolution could unique identify the column (along with the user/tracking-session id). DATETIME columns can still be grouped by date. For a "random" unique identifier (which is an option, I suppose) see UUIDs (v4). Commented Jun 19, 2014 at 9:36
  • What if the user retries 3 seconds later? How would a timestamp prevent that? Commented Jun 19, 2014 at 9:43
  • Record the time of the action. This is no different than recording a "unique" number for the action. If there is an additional action pending, it'll need to resolve it either way. Commented Jun 19, 2014 at 10:00
  • And reuse the timestamp if the person retries, is that what you mean? Commented Jun 19, 2014 at 10:01
  • Yes, the app would always send the initial timestamp when performing the insert (this is how it can track that specific event). There are a few quibbles with users manually adjusting a clock and backward time-syncs but .. Commented Jun 19, 2014 at 10:02

1 Answer 1

Reset to default
1

What about the following approach?

Client side:

  1. Create GUID for insert-request
  2. Send insert-request (value + date + GUID)
  3. Wait for response
  4. Response received --> show confirmation to user ("Completed successfully")
  5. No response received --> request insert-response (incl. guid)
  6. insert-response received --> show confirmation to user ("Completed successfully")
  7. No insert-response received --> repeat 5.
  8. Response == "not inserted" --> show error message to user ("Error, try again")

Server side:

  1. Receive insert-request --> insert data (value, date) into table
  2. Send confirmation --> GUID, ok
  3. OR: receive request (GUID inserted?) --> send response guid inserted yes/no
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Exactly what I thought myself - I should then do a combined unique index of date and GUID, yes?
A GUID should do. You just need it do identify the request. No need to store it in the database. Just keep it in memory for a while, in case the client requests a confirmation. This is far from perfect, however.
What would be the perfect solution, then? I'm willing to redesign if you have a better idea.
I think you are trying to replicate what is already implemented inside network protocols. However, I am not an expert on that. But as long as you choose a connection-based protocol like TCP and initializing the connection is succesful, you should be on the safe side.
Uhm. I'm not really sure about that. Let's say the server receives the POST, but the latency to the iPad is 400ms, and while those 400ms go by, the iPad loses connection for whatever reason. How will the server know that the response was not delivered?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.