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 HHS. Gov 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  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 npi@telegenisys.com, and we’ll send you a copy of our texts every time HHS changes their schema. I.e., with every release of their database