Modeling

We indicated the NPI data contains about 400 fields across three CSV tables. The CSV file is certainly not normalized, some database schema design needs to be created.

Reverse-Engineering The CSV Files

So how do you reverse-engineer the CSV files? We have used Object Role Modeling (ORM) to design databases for over 20 years with fantastic success. Object Role Modeling is the best tool I have used to model a database schema.

After studying the provided documentation and analyzing with ORM we uncovered 8 objects. The NPI is one object and there are 7 other objects, all of which there may be multiple records for any one NPI ...

Individual Providers
Organization Providers
Provider Names
Addresses
Phones
Insurers (Payors)
Taxonomy Codes

And the database schema ends up looking like so ...

Provider Types

There is a provider table, a table for individuals and yet another for organizations. Three tables just for the NPI?

Right.

A provider is either an individual or an organization with completely different properties. They are different object types while both having an NPI. Obviously individuals and organizations employ different name conventions. There are very few properties directly related to an NPI. What we have here is inheritance. And yes, databases can persist inheritance.

Provider Names

To further complicate the situation, in the NPPES data, individuals have two different names and organizations have three different names. Individuals have a legal name and an AKA. Organizations have a legal name, a DBA and a parent organization name. That is five different names that an NPI could have,

Looks like a one-to-many relationships going on between the one NPI and the many names it may take on. So names go in their own table.

When you are searching by name and do not know if its an individual or an organization, you may perform up to five searches before you find the provider, Ouch.

Not with Recaster.

With Recaster, the 'names' table contains a record for each name an NPI make take on. Notice how few fields are in the 'names' table. Fewer fields in a table makes for dramatically faster searches. So if all you know is the name contains 'Nancy' you will get back all records with 'Nancy', and the name type could be any of the five types.

SQL Views

We discovered all the different type objects and pulled them out of the CSV. We created tables for these objects and populate the tables with whatever number of these objects each NPI may have.

Then we put it back together again with SQL Views. These join the tables back together again the way we need them. Recaster installs a number of views that you will commonly need.

Customizations

Many organizations use a CRM system. Recaster allows you to add fields to the tables that CRM systems often require; record owner, create date and modify date. You can add these fields to all the tables and default the values as you see fit.