Production.BillOfMaterials

Table: 'Production.BillOfMaterials'

9 fields. 78 bytes.

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

Field Name Data Type Size Original Type Default Nullable Description Check

BillOfMaterialsID int identity 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 getdate() Not null 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 (1.00) Not null Quantity of the component needed to create the assembly. ([PerAssemblyQty]>=(1.00))

ModifiedDate datetime getdate() Not null Date and time the record was last updated.

Field Name	Data Type	Size	Original Type	Default	Nullable	Description	Check
BillOfMaterialsID	int identity		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			getdate()	Not null	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		(1.00)	Not null	Quantity of the component needed to create the assembly.	([PerAssemblyQty]>=(1.00))
ModifiedDate	datetime			getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'Production.BillOfMaterials'

Primary Key Name Field Name

PK_BillOfMaterials_BillOfMaterialsID BillOfMaterialsID

Primary Key Name	Field Name
PK_BillOfMaterials_BillOfMaterialsID	BillOfMaterialsID

Foreign keys for table 'Production.BillOfMaterials', 3 items

Foreign Primary Key Name

BillOfMaterials.ProductAssemblyID Product.ProductID FK_BillOfMaterials_Product_ProductAssemblyID

BillOfMaterials.ComponentID Product.ProductID FK_BillOfMaterials_Product_ComponentID

BillOfMaterials.UnitMeasureCode UnitMeasure.UnitMeasureCode FK_BillOfMaterials_UnitMeasure_UnitMeasureCode

Foreign	Primary	Key Name
BillOfMaterials.ProductAssemblyID	Product.ProductID	FK_BillOfMaterials_Product_ProductAssemblyID
BillOfMaterials.ComponentID	Product.ProductID	FK_BillOfMaterials_Product_ComponentID
BillOfMaterials.UnitMeasureCode	UnitMeasure.UnitMeasureCode	FK_BillOfMaterials_UnitMeasure_UnitMeasureCode

Indexes for table 'Production.BillOfMaterials', 3 items

Index Name Description Clustered Unique Fields

AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate Clustered index. Yes Yes ProductAssemblyID, ComponentID, StartDate

PK_BillOfMaterials_BillOfMaterialsID Nonclustered index created by a primary key constraint. No Yes BillOfMaterialsID

IX_BillOfMaterials_UnitMeasureCode Nonclustered index. No No UnitMeasureCode

Index Name	Description	Clustered	Unique	Fields
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate	Clustered index.	Yes	Yes	ProductAssemblyID, ComponentID, StartDate
PK_BillOfMaterials_BillOfMaterialsID	Nonclustered index created by a primary key constraint.	No	Yes	BillOfMaterialsID
IX_BillOfMaterials_UnitMeasureCode	Nonclustered index.	No	No	UnitMeasureCode

Triggers for table 'Production.BillOfMaterials', 1 item

Name Description Type Enabled

uBillOfMaterials AFTER UPDATE trigger setting the ModifiedDate column in the BillOfMaterials table to the current date. after Update Yes

Name	Description	Type	Enabled
uBillOfMaterials	AFTER UPDATE trigger setting the ModifiedDate column in the BillOfMaterials table to the current date.	after Update	Yes

Dependencies for table 'Production.BillOfMaterials', 7 items

Object Name Type Field Name

dbo.uspGetBillOfMaterials stored procedure N/A

dbo.uspGetWhereUsedProductID stored procedure N/A

Production.CK_BillOfMaterials_BOMLevel check cns N/A

Production.CK_BillOfMaterials_EndDate check cns N/A

Production.CK_BillOfMaterials_PerAssemblyQty check cns N/A

Production.CK_BillOfMaterials_ProductAssemblyID check cns N/A

Production.uBillOfMaterials trigger N/A

Object Name	Type	Field Name
dbo.uspGetBillOfMaterials	stored procedure	N/A
dbo.uspGetWhereUsedProductID	stored procedure	N/A
Production.CK_BillOfMaterials_BOMLevel	check cns	N/A
Production.CK_BillOfMaterials_EndDate	check cns	N/A
Production.CK_BillOfMaterials_PerAssemblyQty	check cns	N/A
Production.CK_BillOfMaterials_ProductAssemblyID	check cns	N/A
Production.uBillOfMaterials	trigger	N/A