Technical Guidance

Entity Relationship Diagram

These tables are available as Excel workbooks.

This Model illustrates the tables and their relationships with each other.

Relationship diagram between NDC Unit of Sale,NDC Linker and NDC Unit of Use

 

NDC Links

This table links the unit of use and unit of sale tables by their primary keys.

Columns
NDC Links
PK
Name
Type
Not Null
Unique
Len
Prec
Scale
Init
Notes
True NDClinkerID Integer True True Primay key for getNDCLinks
False NDCOuterID Integer True False Foreign key to get_NDC_sale
False NDCInnerID Integer True False Foreign key to get_Use_NDC

Constraints
Constraints
Name
Type
Columns
Initial Code
Notes
FK_NDCInnerID Public NDCInnerID Foreign key constraint
FK_NDCOuterID Public NDCOuterID Foreign key constraint
PK_get_NDClinks Public NDClinkerID
UQ_get_NDClinks_NDClinkerID Public NDClinkerID

Relationships
Relationships
Columns
Association
Notes
(NDCInnerID = NDCUseUnit_ID) 1..* NDClinks.FK_NDCInnerID
1 use_NDC.PK_get_use_NDC
(NDCOuterID = NDCouterID) 1..* NDClinks.FK_NDCOuterID
1 sale_ndc.PK_get_sale_ndc

 Top of Page

Sale NDC

This table contains the NDC records for Unit of Sale packages.

Columns
Sale NDC
PK
Name
Type
Not Null
Unique
Len
Prec
Scale
Init
Notes
True NDCouterID Integer True True Primary key for this table. Used as foreign key by get_NDClinks table.
False OuterLabeler text True False 5 Labeler Code for Unit of Sale record
False OuterProduct Text True False 4 Product code for Unit of Sale
False OuterPackage Text True False 2 Package code for Unit of Sale
False OuterPropName Text True False 50 Unit of Sale Proprietary name
False OuterGenericName Text True False 255 Unit of Sale generic name
False OuterLabelerName Text True False 50 Name of Unit of Sale Labeler
False OuterStartDate Text False False 8 Marketing start date of Unit of Sale (YYYYMMDD)
False OuterEndDate Text False False 8 End marketing date of Unit of Sale (YYYYMMDD)
False OuterRoute Text False False 50 Route of adminsistration
False OuterGTIN Text False False 50 GTIN (barcode) for outer package
False last_update_date DateTime True False Date this record was last updated
False CVX code Text True False 4 The CVX code for Unit of Sale
False vaccine_name Text True False 50 the short name from the CVX table for reference
False NDC11 Text True False 13 NDC normalized to 11 characters with dashes between components. (5-4-2)

Constraints
Constraints
Name
Type
Columns
Initial Code
Notes
PK_get_sale_ndc Public NDCouterID
UQ_get_sale_ndc_NDCouterID Public NDCouterID

Relationships
Relationships
Columns
Association
Notes
(NDCOuterID = NDCouterID) 1..* NDClinks.FK_NDCOuterID
1 sale_ndc.PK_get_sale_ndc

 Top of Page

Use NDC

This table contains the NDC for Unit of Use. It includes CVX for this vaccine.

Columns
Use NDC
PK
Name
Type
Not Null
Unique
Len
Prec
Scale
Init
Notes
True NDCUseUnit_ID Integer True True This is the primary key for this table. It is a foreign key in the get_NDClinks table.
False UseUnitLabeler Text True False 5 This is the Labeler code for the Unit of Use.
False UseUnitProduct Text True False 4 This is the product code for the unit of use.
False UseUnitPackage Text True False 2 This is package code for the unit of use.
False UseUnitPropName Text True False 50 The Proprietary name for the unit of sale
False UseUnitGenericName Text True False 255 The generic name for the unit of sale vaccine
False UseUnitLabelerName Text True False 50 labeler name for the unit os use
False UseUnitstartDate Text False False 8 The begin sale date for the unit of use (YYYYMMDD)
False UseUnitEndDate Text False False 8 The end sale date for the unit of use (YYYYMMDD)
False UseUnitGTIN Text False False 50 GTIN (barcode) for Unit of use.
False CVX_code Text True False 4 CVX for unit of sale NOTE that for some vaccines that have separate vaccine components, the CVX will map to the combined vaccine.
False vaccine_name Text True False 50 CVX vaccine name
False NDC11 Text True False 13 NDC11 for unit of use normalized (5-4-2)
False last_updated_date DateTime True False Date this record was last updated.

Constraints
Name
Type
Columns
Initial Code
Notes
PK_get_use_NDC Public NDCUseUnit_ID
UQ_get_use_NDC_NDCUseUnit_ID Public NDCUseUnit_ID

Relationships
Columns
Association
Notes
(NDCInnerID = NDCUseUnit_ID) 1..* NDClinks.FK_NDCInnerID
1 use_NDC.PK_get_use_NDC

 Top of Page

Page last reviewed: January 5, 2017