Production BillOfMaterials (table)

wikibot

TableProduction.BillOfMaterials
DescriptionItems required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.

Columns

ColumnData TypeNullableDefaultDescription
BillOfMaterialsIDintnot null Primary key for BillOfMaterials records.
ProductAssemblyIDintnull Parent product identification number. Foreign key to Product.ProductID.
ComponentIDintnot null Component identification number. Foreign key to Product.ProductID.
StartDatedatetimenot null(getdate())Date the component started being used in the assembly item.
EndDatedatetimenull Date the component stopped being used in the assembly item.
UnitMeasureCodenchar(3)not null Standard code identifying the unit of measure for the quantity.
BOMLevelsmallintnot null Indicates the depth the component is from its parent (AssemblyID).
PerAssemblyQtydecimal(8, 2)not null((1.00))Quantity of the component needed to create the assembly.
ModifiedDatedatetimenot null(getdate())Date and time the record was last updated.

Primary Key

Primary KeyColumns
PK_BillOfMaterials_BillOfMaterialsIDBillOfMaterialsID

Indexes

IndexTypeColumns
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDateUniqueProductAssemblyID, ComponentID, StartDate
IX_BillOfMaterials_UnitMeasureCode UnitMeasureCode

Check Constraints

Check ConstraintExpressionDescription
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

RelationColumnReferenced Column
Production.ProductComponentIDProductID
Production.ProductProductAssemblyIDProductID
Production.UnitMeasureUnitMeasureCodeUnitMeasureCode

References

Dependency TypeObject TypeReferenced Object
SchemaSchemaProduction

Dependencies

Reference TypeObject TypeReferencing Object
SelectProceduredbo.uspGetBillOfMaterials
SelectProceduredbo.uspGetWhereUsedProductID