I have a table holding 'People' data (about 70K records) that coexists with an 'Address' table so there's one
AddressID per every person in the 'People' table.
The idea was to centralize the data in this single 'Population' database, problem started once duplicated records started to appear due to a poorly executed Import process (Data coming from different sources), this left the table with as much as 7 records for a single Person.
Additionally the Address table started collecting 'all sorts', so let say I live in "24 Wickam Heights" you can found this address in the following ways:
- 24 Wickam
- 24 Wickam St
- 24 Wickam Street
- 24 Whikam H.
- 24 Wikam Str.
In some cases as bad as over 20 different versions of the same street...
The most beautiful part is that the data from this database is being referenced from at least 5 other databases in the same server, making every change a very risky process.
So I'm thinking, what steps can be taken to get rid of the duplicates?, What alternatives there is for avoiding the address table accumulating such an amount of data inconsistency?.
Maybe even to ask, is there any salvation for such a big mess? It really is a nightmare.