Production BillOfMaterials (table)

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