Creating Unique Key Identification

Question

Hi Guys,

I am working on a health service project were I need a data validation technic to identify patient and the  history of that patient.

Scenario:

If a patient went to A hospital for heart treatment, hospital will give him a date for operation. During course of time if meet with accident and broke his leg he will be admitted to different hospital. Now issue is how I can validate data because as you can imagine there would possibility of same name, DOB, Add etc also keep in mind all this hospitals use different databases. I need to create a unique key to identify that patient and maintain the history of that patient.

How to solve this issue?

On top of my head we got 430 separate databases and patient can move anywhere from Hospital A to B OR C and than A.

Thanks and Regards,

D

Answer 1

Unfortunately, this seems like a pretty complex issue.  In some scenarios (out of the medical industry) you could match the Patient on First and Last name or use Fuzzy Lookup.  However, when dealing with patient history this is not an option.

There must be some type of unique identifier for each patient to make this possible, i.e. an SSN or Insurance Provider ID.  Without a unique identifier across all databases there is really no easy way (or any way) to link all of the related patient records to each patient accurately.  For example, if there is a patient named Jim Smith in three of the databases you could obviously not assume they were the same patient.

Do some investigation and let me know how you are going to be able to link the patient based on a unique ID and I can give you additional suggestions on how to further build a data warehouse or perform validation.

Question:  Do all of the 430 databases have the same schema?

---

Check out my blog!

Answer 2

Hi Michael,

Thanks very much for your time and input.

Yes, I was thinking on a same line like inurance provider or SSN no. Basically, I will get access to database and all the details by Tuesday in regards to requirements, current database's schema  etc. I will let you know the exact picture by Tuesday.

I will keep posting and update the project status is this is right place or do you have any other contact details.

Thanks and Regards,

D

Answer 3

Please don't use SSN for a unique identifier.  It isn't unique, can be forged, is sensitive data that you shouldn't have access to, etc...

Instead, try creating a GUID for each patient, which will be unique.  Then, you can use the attributes of the patient to lookup the appropriate GUID.  You can then use the GUID value when referencing this patient in other tables, rather than storing something like an SSN.

---

Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

Answer 4

Hey Phil,

I agree with you on not using the SSN everywhere 100%, however, until he can match all his patients initially and create a distinct (key word distinct) list of patients he still won't be able to solve his problem.  If you create a unique GUID for two patients in two separate databases that happen to be the same person (Rob vs. Robert vs. Bob as mentioned above), then you still can't build an accurate patient history.

If his patient history (a new database, schema, or table) includes a patient table you could use a unique field (the two that I could think of that might possibly be unique are listed above) to find all distinct patients and then assign each one of these resulting distinct patients a GUID for use in all other tables.

Thoughts?

---

Check out my blog!