Short answer
A durable healthcare provider schema separates the provider from source records, organizations, locations, roles, specialties, and affiliations. Each source observation keeps its raw values, URL, and collection timestamp. Normalized entities are linked through auditable match records. This prevents a hospital-directory profile, NPPES record, or professional-directory listing from silently becoming an unsupported universal fact.
Why one provider row is not enough
A spreadsheet often begins with:
name, specialty, hospital, address, phone, npiThat format is convenient, but its meaning becomes unclear when:
- One physician has several specialties.
- A physician practices at three locations.
- Two hospitals publish profiles for the same person.
- The hospital specialty differs from the NPPES taxonomy.
- The practice phone differs by location.
- A faculty title appears only on an academic profile.
- One source changes while another remains the same.
Adding address_2, hospital_3, and specialty_4 postpones the problem. It does not solve it.
The schema should reflect the real relationships.
Provider
├── Source records
├── Identifiers
├── Provider roles
│ ├── Organization
│ ├── Location
│ ├── Specialty
│ └── Public contact points
└── Match and change historyThe core entities
| Entity | What it represents | Example |
|---|---|---|
| Provider | An internal canonical professional entity | One physician |
| Source | A registry, hospital directory, board, or professional website | NPPES or a health-system directory |
| Source record | What one source published at a particular time | One provider profile snapshot |
| Identifier | An identifier tied to a provider or organization | NPI |
| Organization | Hospital, health system, practice, group, or academic institution | Example Medical Group |
| Location | A physical or service location | Clinic address |
| Provider role | A provider's source-supported role for an organization or at a location | Cardiologist at Clinic A |
| Specialty | Raw and normalized specialty representation | "Heart Failure" -> Cardiology hierarchy |
| Match decision | Why a source record was linked to a canonical entity | Published NPI exact match |
| Change event | A field or relationship difference between snapshots | Phone changed |
The HL7 FHIR `PractitionerRole` resource (opens in a new tab) follows a similar relationship model. It describes services or roles a practitioner performs for an organization at locations and allows repeated specialties, services, and telecom fields.
Source records are the evidence layer
Do not normalize directly into the provider table and discard the source observation.
A source record should retain:
- Source ID
- Source-native record ID when available
- Canonical source URL
- Collection run and timestamp
- Raw name and credentials
- Raw specialty and organization text
- Raw locations and contact values
- Raw identifiers
- Parser version
- Raw payload or permitted snapshot reference
- Stable comparison hash
This record answers:
What did this source publish, where, and when?
The provider table answers a different question:
Which source records do our approved matching rules currently believe refer to the same provider?
A relational schema
The following PostgreSQL example is deliberately compact. A production schema may require tenant boundaries, audit tables, soft-delete policies, regional address rules, and additional constraints.
create table sources (
source_id text primary key,
source_name text not null,
source_type text not null,
base_url text,
active boolean not null default true
);
create table collection_runs (
run_id uuid primary key,
source_id text not null references sources(source_id),
started_at timestamptz not null,
completed_at timestamptz,
status text not null,
adapter_version text not null
);
create table source_provider_records (
source_record_id uuid primary key,
source_id text not null references sources(source_id),
run_id uuid not null references collection_runs(run_id),
source_native_id text,
source_url text not null,
collected_at timestamptz not null,
provider_name_raw text not null,
credentials_raw jsonb not null default '[]',
specialties_raw jsonb not null default '[]',
organizations_raw jsonb not null default '[]',
locations_raw jsonb not null default '[]',
identifiers_raw jsonb not null default '{}',
extra_raw jsonb not null default '{}',
record_hash text not null,
parser_version text not null,
unique (run_id, source_url)
);
create table providers (
provider_id uuid primary key,
display_name text not null,
first_name text,
middle_name text,
last_name text,
suffix text,
created_at timestamptz not null,
updated_at timestamptz not null
);
create table provider_identifiers (
provider_id uuid not null references providers(provider_id),
identifier_type text not null,
identifier_value text not null,
source_record_id uuid references source_provider_records(source_record_id),
status text not null default 'observed',
primary key (provider_id, identifier_type, identifier_value)
);Notice that provider_identifiers retains the source record that supported the identifier. An NPI should not appear in the canonical entity without an evidence trail.
Modeling organizations and locations
create table organizations (
organization_id uuid primary key,
organization_name text not null,
organization_type text,
created_at timestamptz not null,
updated_at timestamptz not null
);
create table locations (
location_id uuid primary key,
address_line_1 text,
address_line_2 text,
city text,
region text,
postal_code text,
country_code text,
latitude numeric,
longitude numeric
);
create table provider_roles (
provider_role_id uuid primary key,
provider_id uuid not null references providers(provider_id),
organization_id uuid references organizations(organization_id),
location_id uuid references locations(location_id),
role_raw text,
department_raw text,
faculty_title_raw text,
phone_raw text,
phone_normalized text,
fax_raw text,
fax_normalized text,
valid_from date,
valid_to date,
source_record_id uuid not null references source_provider_records(source_record_id),
observed_at timestamptz not null
);The source_record_id is essential. Without it, a provider-organization relationship can outlive its evidence and become impossible to audit.
If the source does not publish a formal start or end date, leave valid_from and valid_to empty. The collection timestamp means "observed on this date," not "relationship began on this date."
Modeling specialties
Keep the source term and normalized taxonomy separate.
create table specialties (
specialty_id uuid primary key,
taxonomy_name text not null,
taxonomy_version text not null,
normalized_label text not null,
parent_specialty_id uuid references specialties(specialty_id)
);
create table provider_role_specialties (
provider_role_id uuid not null references provider_roles(provider_role_id),
specialty_id uuid references specialties(specialty_id),
specialty_raw text not null,
normalization_rule_version text,
primary key (provider_role_id, specialty_raw)
);A source value such as "Advanced Heart Failure" may be useful as written even if the reporting taxonomy rolls it up to Cardiology. Never destroy the raw term.
Modeling NPI correctly
CMS defines the NPI as a unique, 10-position, intelligence-free identifier for covered healthcare providers. It is useful for identity, but several cautions remain:
- Type 1 NPIs represent individuals; Type 2 NPIs represent organizations.
- An NPI does not encode specialty or location in its digits.
- NPI issuance does not validate licensing or credentialing.
- A hospital profile may not publish an NPI.
- Name-based matching may produce several NPPES candidates.
- NPPES addresses and hospital-profile locations can legitimately differ.
The CMS NPPES file page (opens in a new tab) currently provides a full monthly Version 2 file, weekly incremental files, a monthly deactivation file, and reference files including non-primary practice locations.
Store NPPES as a source. Do not treat it as permission to replace every directory field with the NPPES value.
Match decisions need their own table
create table provider_match_decisions (
match_decision_id uuid primary key,
source_record_id uuid not null references source_provider_records(source_record_id),
provider_id uuid references providers(provider_id),
match_status text not null,
match_rule text not null,
matched_fields jsonb not null default '[]',
conflicting_fields jsonb not null default '[]',
confidence_score numeric,
reviewed_by text,
reviewed_at timestamptz,
created_at timestamptz not null
);Suggested statuses:
matched_exact_identifiermatched_rulereview_requirednew_providerrejected_candidateunresolved
Do not turn an arbitrary confidence score into certainty. The rule, fields, and conflicts are more important than a number without calibration.
Canonical values versus current source values
Avoid one unqualified phone field on providers.
A provider may have:
- NPPES practice phone
- Hospital scheduling phone
- Practice-location phone
- Public fax
- Separate phones at several locations
If an application needs a preferred phone, create a selection layer:
preferred_phone
selected_from_source_record_id
selection_rule_version
selected_atThe preferred value is a product decision. It should not erase the underlying observations.
JSON delivery model
For an API or nested file, a record can expose relationships explicitly.
{
"provider_id": "8a9b7c10-7f2c-4ef9-a03d-123456789abc",
"display_name": "Alexandra M. Smith, MD",
"identifiers": [
{
"type": "NPI",
"value": "1234567890",
"status": "observed"
}
],
"roles": [
{
"organization": "Example University Medical Center",
"department": "Cardiology",
"faculty_title": "Associate Professor",
"specialties": [
{
"raw": "Advanced Heart Failure",
"normalized": "Cardiology"
}
],
"locations": [
{
"city": "Example City",
"region": "NY",
"phone": "+12125550100"
}
],
"source": {
"url": "https://example.org/provider/alexandra-smith",
"collected_at": "2026-07-21T00:00:00Z"
}
}
]
}Example values are fictional and illustrate the shape only.
Record grain must be explicit
Before exporting CSV, answer:
What does one row represent?
Valid options include:
- One source profile
- One canonical provider
- One provider-organization relationship
- One provider-location relationship
- One provider-role-location-specialty combination
Two correct exports can have different row counts because they use different grain. Put the grain in the data dictionary.
Provenance fields every delivery should contain
At minimum:
source_id
source_url
source_native_id
collected_at
collection_run_id
parser_version
raw_value
normalized_value
normalization_rule_versionNot every field belongs in the buyer-facing CSV, but the pipeline should retain enough of this information to answer a challenge later.
Validation rules
Provider rules
- Raw provider name is not empty.
- NPI has ten digits before being accepted as a candidate format.
- Published NPI source is recorded.
- Ambiguous name matches are not forced.
Relationship rules
- Organization and location links retain a supporting source record.
- Missing affiliation is not converted to "no affiliation."
- Collection time is not used as relationship start time.
Location rules
- Region and country formats are explicit.
- Postal codes remain strings.
- Location deduplication does not rely only on formatted address text.
Change-history rules
- New observations do not overwrite historical source records.
- Missing profiles remain
missinguntil the confirmation rule is met. - Parser-version changes are visible in QA reporting.
Frequently asked questions
Should the provider table use NPI as its primary key?
Usually no. Use an internal provider ID and store NPI in an identifiers table. This supports unresolved records, source profiles without a published NPI, organizations with Type 2 NPIs, and future correction of a bad match without changing the internal primary key.
How should multiple practice locations be stored?
Model locations separately and link them through provider roles or provider-location relationships. Do not keep adding numbered address columns. Each relationship should retain the source record and observation time that support it.
Should raw source fields be kept after normalization?
Yes. Raw fields preserve meaning, support audits, and allow normalization rules to be changed later. A normalized specialty or organization name should be an additional value, not a destructive replacement.
How do you store conflicting specialties?
Keep each source observation. A hospital may publish a clinical specialty while NPPES publishes one or more taxonomy codes. A reporting layer can map both to an agreed taxonomy while retaining their origin.
Does a source profile establish current affiliation?
It establishes that the source displayed the relationship when collected. It does not automatically prove employment, network participation, privileges, or the exact relationship type. Store the source wording and timestamp.
Next step
Read How to Build a Multi-Source Healthcare Provider Data Pipeline for collection architecture and Provider Directory Refresh and Change Detection for snapshots and history. Orzaen's Healthcare Provider Data Collection offer covers source-scoped delivery.