occasional duplicate entries upon saving new records (supertimesheet)

We’re seeing a problem that arises aprox. 0.1% of the time, but is annoying enough and important enough data that we need to find a solution.

We’re seeing this in a custom plugin module called Supertimesheet (I already asked the original dev if he could help out but he currently has no time).

In supertimesheet, a can make multiple entries in one save procedures (in a view called “matrix view”, where the user sees a matrix of input fields for all projects for the day or the week). So the user can easily save 20+ database records in one save procedure.

Every once in a while the database ends up with exact duplicates of all entries made in a single save event. This leads to duplicates of the work times entered in that save step and false data. (sounds great for employees :slight_smile: until it’s noticed and it’s a great nuisance to change)

Apart from the exact codebase of this module (except if someone knows this supertimesheet module well), can you think of any reason why this may happen? And how it could be prevented for good?

Just found this. Might be the way to go? Begs the question what happens if the PHP code tries to save duplicate entries (for whatever reason), but the DB doesn’t allow it. => ugly database error? (I guess that’s better than having unnoticed duplicates in the DB).

http://www.alphadevx.com/a/382-Adding-a-composite-unique-key-to-an-existing-MySQL-table

OK. next prob. if I set a composite unique contraint for

user_id
date_booked
parent_id

I sort of solve my problem. Except that I can’t delete an entry and then re-add one later.

If I make composite unique contraint including the deleted field I can have two identical timesheet entries, one deleted, one not.
But if a user deletes an entry once, then re-adds data for the same date and project, the user can no longer delete another entry… bound to lead to problems too.

Any other ideas?

So my current idea is to combine the composite unique contraint for

user_id
date_booked
parent_id

with an after_delete logic_hook that removes the row from the DB completely. That way there’s never an issue with deleting data for the same set of unique contraints.

Got the logic_hook working. Now I need to clean the table from duplicates and setup the composite unique constraint…