4

On a mobile device, a set of operations has been saved in the local DB with a wrong date (because the system date was set in the future). Then the device regularly doing synchronisation with a server DB, the erroneous operations have been propagated to the server.

Later the wrong date has been detected by the mobile user, and the system date set back correctly, and the user resumed his normal flow of work, pushing even more data to the server.

What should be the best strategy to clean the data from this incorrect state. As suggested in Accountants Don't Use Erasers, a wrong entry (invoice for example) should be compensated by a negative corresponding entry (credit note).

Then what if the problem is with the date of the entry ? How to compensate a wrong date ?

More generally what is the best strategy to rollback a database longtime after data have been committed ? and how to rollback when database is synchronized over multiple systems ?

NB : I posted first in stackoverflow.com but I believe here is a more appropriate place.

Glorfindel
  • 3,137
  • 6
  • 25
  • 33
Yahia
  • 143
  • 3
  • The database allows operations that are dated in the future to be entered? – kevinskio Jan 13 '16 at 13:44
  • Since the operation was done with a wrong system date, the database saved the current (wrong) date. – Yahia Jan 13 '16 at 13:45
  • In this example are you correcting the invoice date/time, the date/time the invoice was entered or both? Are they one in the same? – JeffO Jan 13 '16 at 17:32
  • The table contains only a invoice_date field, so we need only to correct the invoice date/time, not the date/time the invoice was entered. The issue is that beyond the invoice, there is also order, delivery, stock, cash, ... It would be tedious finding manually and correcting all the rows impacted. What would be the technics to implement, so next time such incident occurs, it would be easy to recover (ex: log all operations on smartphone and on server database ?) – Yahia Jan 13 '16 at 17:42

1 Answers1

1

First you need to decide how to make this change. Either update all the necessary invoice date fields or cancel the invoice and copy/rebuild all the data with a different date and possibly invoice number.

Once you can code making this change, you can then decide on how to apply it to many invoices. If you think the problem is going to continue to stem from mobile app imports, start attaching some sort of Batch ID to all the records imported from each device at each import. Date and time stamp along with any other device and/or user indicator you can include. You'll probably end up with a batch table and a batch id as a foreign key in your invoice table.

Ideally, you could just filter a group of invoices out by user and some sort of date range and change them all from Date A to Date B.

Someone with domain knowledge needs to get involved in this process on what to do and the ramifications and then some manager gets to make a decision.

JeffO
  • 36,816
  • 2
  • 57
  • 124
  • I was looking for something like [Temporal Tables](https://devjef.wordpress.com/2015/07/13/time-traveling-with-sql-server-2016-temporal-tables/) in SQL 2016. Is there a way to achieve that in previous MS SQL versions ? – Yahia Jan 14 '16 at 10:11
  • @Yahia- most of the pieces should be there. It won't be easy, but with an additional table (history) and a few triggers, you should be able to reproduce this functionality yourself. – JeffO Jan 18 '16 at 11:45
  • Thanks @JeffO for the direction, I ended up creating audit trail triggers for all tables (generic procedure [here](https://snipts.wordpress.com/2016/01/26/create-audit-trail-triggers-for-all-tables-in-a-db/)), then reproduced the cycle with the same erroneous date between the smartphone and the server. It allowed to identify all impacted data, so the correction was exhaustive. – Yahia Jan 28 '16 at 09:55