Technical Guidance

On This Page

Entity Relationship Diagram

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

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

NDC Links
PK	Name	Type	Not Null	Unique	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	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

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

Sale NDC
PK	Name	Type	Not Null	Unique	Len	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

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

Use NDC
PK	Name	Type	Not Null	Unique	Len	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.

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

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

Page last reviewed: September 5, 2024