I'm not sure if I am posting this message in the right place. Please let me know if I should redirect this question elsewhere.
I'm currently working on a large database cleanup and consolidation project where I own pretty much everything to do with the project. The database represents a healthcare network.
Part of the data cleanup is merging duplicate doctors. The data is split into a master table as well as additional tables for addresses, ID's, etc. The current database contains data sourced from 3 different various data sources over time so the range of duplicates is pretty vast.
I have created a semi-effective duplicate finding routine in SQL Server T-SQL but it is slow and not very flexible. Ideally I'd like the same duplicate finding/matching routine to be able to be used for various purposes such as cleaning up current data, validating new data entered by users (checking if provider already exists), and properly matching up doctors from external data sources to doctors into our internal database.
Having a highly accurate and efficient duplicate control is a crucial part of this project and what I've created so far doesn't work as well as I would like. There's so many variations of names, ID's, etc. that I'm not sure how to improve it. I imagine there's some kind of confidence level technique I can use or something.
I've tried searching online for some tips but I can't seem to find anything specific to my situation. Anyone have any ideas or comments on how they would proceed? I currently am able to develop with SQL Server (T-SQL) and VB.NET. I also am familiar with creating CLR routines (coupling VB.NET with SQL Server). I also am considering outsourcing it to have someone else work on this component, but don't know where to go for that either.
Thanks,
Zach
I'm currently working on a large database cleanup and consolidation project where I own pretty much everything to do with the project. The database represents a healthcare network.
Part of the data cleanup is merging duplicate doctors. The data is split into a master table as well as additional tables for addresses, ID's, etc. The current database contains data sourced from 3 different various data sources over time so the range of duplicates is pretty vast.
I have created a semi-effective duplicate finding routine in SQL Server T-SQL but it is slow and not very flexible. Ideally I'd like the same duplicate finding/matching routine to be able to be used for various purposes such as cleaning up current data, validating new data entered by users (checking if provider already exists), and properly matching up doctors from external data sources to doctors into our internal database.
Having a highly accurate and efficient duplicate control is a crucial part of this project and what I've created so far doesn't work as well as I would like. There's so many variations of names, ID's, etc. that I'm not sure how to improve it. I imagine there's some kind of confidence level technique I can use or something.
I've tried searching online for some tips but I can't seem to find anything specific to my situation. Anyone have any ideas or comments on how they would proceed? I currently am able to develop with SQL Server (T-SQL) and VB.NET. I also am familiar with creating CLR routines (coupling VB.NET with SQL Server). I also am considering outsourcing it to have someone else work on this component, but don't know where to go for that either.
Thanks,
Zach