Technical Guidance
On This Page
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](downloads/NDC/entity_relation.gif)
NDC Links
This table links the unit of use and unit of sale tables by their primary keys.
Columns
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
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
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 |
Sale NDC
This table contains the NDC records for Unit of Sale packages.
Columns
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
Name |
Type |
Columns |
Initial Code |
Notes |
---|---|---|---|---|
PK_get_sale_ndc | Public | NDCouterID | ||
UQ_get_sale_ndc_NDCouterID | Public | NDCouterID |
Relationships
Columns |
Association |
Notes |
---|---|---|
(NDCOuterID = NDCouterID) | 1..* NDClinks.FK_NDCOuterID 1 sale_ndc.PK_get_sale_ndc |
Use NDC
This table contains the NDC for Unit of Use. It includes CVX for this vaccine.
Columns
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 |
Page last reviewed: January 5, 2017
Content source: National Center for Immunization and Respiratory Diseases