Production.BillOfMaterials (table)

From dbscript Online Help
Jump to: navigation, search

wikibot

Table Production.BillOfMaterials
Description Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.

Columns

Column Data Type Nullable Default Description
BillOfMaterialsID int not null Primary key for BillOfMaterials records.
ProductAssemblyID int null Parent product identification number. Foreign key to Product.ProductID.
ComponentID int not null Component identification number. Foreign key to Product.ProductID.
StartDate datetime not null (getdate()) Date the component started being used in the assembly item.
EndDate datetime null Date the component stopped being used in the assembly item.
UnitMeasureCode nchar(3) not null Standard code identifying the unit of measure for the quantity.
BOMLevel smallint not null Indicates the depth the component is from its parent (AssemblyID).
PerAssemblyQty decimal(8, 2) not null ((1.00)) Quantity of the component needed to create the assembly.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.

Primary Key

Primary Key Columns
PK_BillOfMaterials_BillOfMaterialsID BillOfMaterialsID

Indexes

Index Type Columns
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate Unique ProductAssemblyID, ComponentID, StartDate
IX_BillOfMaterials_UnitMeasureCode UnitMeasureCode

Check Constraints

Check Constraint Expression Description
CK_BillOfMaterials_BOMLevel ([ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1)) Check constraint [ProductAssemblyID] IS NULL AND [BOMLevel] = (0) AND [PerAssemblyQty] = (1) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel] >= (1)
CK_BillOfMaterials_EndDate ([EndDate]>[StartDate] OR [EndDate] IS NULL) Check constraint EndDate] > [StartDate] OR [EndDate] IS NULL
CK_BillOfMaterials_PerAssemblyQty ([PerAssemblyQty]>=(1.00)) Check constraint [PerAssemblyQty] >= (1.00)
CK_BillOfMaterials_ProductAssemblyID ([ProductAssemblyID]<>[ComponentID]) Check constraint [ProductAssemblyID] <> [ComponentID]

Foreign Keys

Relation Column Referenced Column
Production.Product ComponentID ProductID
Production.Product ProductAssemblyID ProductID
Production.UnitMeasure UnitMeasureCode UnitMeasureCode

References

Dependency Type Object Type Referenced Object
Schema Schema Production

Dependencies

Reference Type Object Type Referencing Object
Select Procedure dbo.uspGetBillOfMaterials
Select Procedure dbo.uspGetWhereUsedProductID


automatically generated

Table Production.BillOfMaterials
Description Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.


Column Data Type Nullable Default Description / PK / Index
BillOfMaterialsID int not null Primary key for BillOfMaterials records.
PK_BillOfMaterials_BillOfMaterialsID
ProductAssemblyID int null Parent product identification number. Foreign key to Product.ProductID.
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
ComponentID int not null Component identification number. Foreign key to Product.ProductID.
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
StartDate datetime not null (GETDATE()) Date the component started being used in the assembly item.
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
EndDate datetime null Date the component stopped being used in the assembly item.
UnitMeasureCode nchar(3) not null Standard code identifying the unit of measure for the quantity.
IX_BillOfMaterials_UnitMeasureCode
BOMLevel smallint not null Indicates the depth the component is from its parent (AssemblyID).
PerAssemblyQty decimal(8, 2) not null (1.00) Quantity of the component needed to create the assembly.
ModifiedDate datetime not null (GETDATE()) Date and time the record was last updated.


Relation Column Referenced Column
Production.Product ComponentID ProductID
Production.Product ProductAssemblyID ProductID
Production.UnitMeasure UnitMeasureCode UnitMeasureCode


Dependency Type Object Type Referenced Object
Schema Schema Production


Reference Type Object Type Referencing Object
Select Procedure dbo.uspGetBillOfMaterials
Select Procedure dbo.uspGetWhereUsedProductID