CODEWITHSUNDEEP
mysql
nickdnk
nickdnk

Reputation: 4300

Preventing duplicate INSERTS MySQL

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.

Upvotes: 0

Views: 69

Answers (1)

marsze
marsze

Reputation: 17055

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

Upvotes: 1

Related Questions