Ive inherited a table called contacts which is used throughout the site. There are a lot of duplicates in this table we want to remove(i.e. the email field was not set to unique so multiple accounts were created etc etc).
The plan is to select the contact we want to keep and then select the ones we want to remove and transfer the needed data over before deleting the unwanted records. The trouble with this is that there a lots of associated tables, complexed self referential joins etc that the contacts table relates to. If its not done correctly then further down the line there could be serious issues as data may be lost etc...
A thought of mine was to simply merge them but instead of deleting the record we would point it to the main record we are keeping and then set the records we dont need to "inactive" or something so we know they are no longer used and point to an active record.
I can think of how to set it this up in the table but can anyone think of a super cool way to make sure the inactive record always returns the active record it points to when ever it its referenced or needed?
Any advice appreciated,