If you store carrier scan events, you will eventually meet a timeline where every row appears twice: once in English, once in another script, with the identical timestamp. Some postal operators publish each event in more than one language, and if you persist what the carrier published, you persist both.

The obvious fix is a dedup rule. This is the story of why we measured one, found it destroys real data, and shipped nothing.

Why the naive key is wrong in both directions

Most event tables end up keyed on something like:

UNIQUE KEY uk_event ( tracking_number , event_date , description )

Enter fullscreen mode

Exit fullscreen mode

That key is wrong twice over.

It under-dedups. Two rows for one event differ in description (different language), so both survive. The customer counts rows and concludes the parcel went through sorting twice as often as it did.

And it over-dedups the moment you "fix" it.…