Normalizing guidance for NPI database

The National Provider Identifier (NPI) standard is a Health Insurance Portability and Accountability Act (HIPAA) Administrative Simplification Standard that does help simplify things. You can go to the NPPES NPI Registry and search for any healthcare provider in the USA that does billing by their NPI Number or search by location, provider name, or Taxonomy. For most people who need to look up a healthcare provider this is sufficient, and best of all it’s updated daily. If you need to dig deeper into the data also provides a Data Dissemination zip file that you can download and search through however you want. The problem is that the tools you normally use to look at data files probably won’t do you any good because the source file is both too long and too wide for easy use.

The length of the file is understandable. There is after all at least one line per healthcare institution or doctor’s office. For most uses, the data can be filtered out for say all Hospitals in Texas. That will bring the length down to a manageable size, but the npidata file is still too wide. At last count, this file had 329 columns, and it tends to grow with each release. Each of these columns serves some useful function. For example “NPI” has the NPI number, and “Other Provider Identifier State_1” has the state that this provider’s “Other Provider Identifier_1” applies to. This is because healthcare providers are registered under the states, so it’s occasionally useful to find the records from the state they operate in. However, some providers operate in two states or have two registrations in the same state. Therefore this file includes “Other Provider Identifier_2” and a matching “Other Provider Identifier State_2”.
Unfortunately, this same file continues (at last count) up to “Other Provider Identifier_50,” and this isn’t the only field group that repeats over and over inside this same file. Knowing a provider’s 50 state-level identifiers is much more important than how these are ordered. Would it really change anything if the 45th code group was switched with the 23rd?

Fortunately, there is a cure for the ailment of repeating field groups in database schemas. Apply Database normalization. For example, split all the “Other Provider Identifier N” fields into a new database table that just has NPI, Other Provider Identifier, Other Provider Identifier Type Code, Other Provider Identifier State and Other Provider Identifier Issuer. Then do a join between this Other Provider Identifier and the much reduced main NPI table whenever these state-level identifiers are needed.

At Telegenisys we’re working on database scripts to normalize the NPI database. Email us at, and we’ll send you a copy of our texts every time HHS changes their schema. I.e., with every release of their database

past medical history

Past medical history helps assess life expectancy

Past Medical History (PMH) is perhaps the most important section of doctors notes impacting longevity assessment.  When looked at medical conditions...

Impact of Family History on Life Expectancy

A hereditary disease is caused by genetic factors transmitted from parent to offspring. Family history is, therefore, an important consideration in...
life expectancy social history

The impact that social history has on life expectancy

In many parts of the world, life expectancy has been increasing steadily over the past few decades, due to increases in technology, medication, and...

Telegenisys supports law firm privacy compliance

Stephen Treglia writes in the New York Law Journal that "businesses are demanding law firms sign written agreements affirming their compliance with...

Telegenisys Geo codes lanes on more than 50,000 miles of US Highways

Satellite and Ariel image analysis is a core competency of Telegenisys Inc. Telegenisys frequently geo codes artifacts (objects) on the earth...

Containing health record costs in mass litigation

Gathering healthcare related evidence can run up considerable costs even before determining if there is a reasonable chance of making a valid claim....

Pin It on Pinterest