ScrewTurn Wiki

Navigation

Main Page

Quick Search

Advanced Search »

PoweredBy

PoweredBy

Discuss (0)View Page Code History

Production.BillOfMaterials_(table)

Modified: 2011/08/27 10:14 by wikibot - Uncategorized

» 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

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

ScrewTurn Wiki version 2.0.36. Some of the icons created by FamFamFam.