Re: slightly OT - cleaning up "dirty" keys?

bugbear wrote:
> EdStevens wrote:
> > And that means looking at the human factors as well. Not knowing anything about
> > the application, I'm wonder WHY the operators are failing to find an
> > existing record and end up createing a duplicate.
>
> There's an automatic feed from external sources, which
> include all the "secondary" data (address, phone etc).
>
> If the (primary)name doesn't match, a new record is created,
> from all the fields in the external feed.
>
> Combine this with multiple external sources,
> run for 5 years, and you have a mess.
>
> That's where I'm STARTING.
>
> Now I have to "make it better".
>
> BugBear

OK. There are obviously a lot of things about this app we don't know. In an earlier post you said "Different operators are FAILING to locate the original record, and are entering a new one. " Now you're saying it's an automatic feed from multiple external sources. In any event, I'd want to know what is being done to change the way the data is initially captured. Because if nothing is being done to fix that, your attempts to clean the data will be futile.

That being said, given the immediate task of identifying duplicates I'd go back to my original suggestion of looking at EVERY column in the database and giving thought to how it might be used. I've already described potential normalization of address and phone number. Perhaps something along the same line for name. Are all of the name in the format of <some variation of first name, middle initial><last name>? If so, perhaps you could pick out the last name, then concatenate it with the first letter of whatever passes for first name. Maybe concatenate that with a normalilze phone and or address ... Received on Fri Mar 03 2006 - 12:27:04 CST