Production.BillOfMaterials Table

Information

Name	BillOfMaterials
Schema	Production
Row Count	0
Data Size
Index Size
Reserved Size
Unused Size
Created	29.3.2010. 21:58:18
Modified	29.3.2010. 21:58:54

Extended Properties

Name	Type	Property Name	Value
BillOfMaterials	TABLE	MS_Description	Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.

Columns

Name	Data Type	Collation	References	Default
BillOfMaterialsID	int
ProductAssemblyID	int		Production.Product.ProductID
ComponentID	int		Production.Product.ProductID
StartDate	datetime			(getdate())
EndDate	datetime
UnitMeasureCode	nchar(3)	SQL_Latin1_General_CP1_CI_AS	Production.UnitMeasure.UnitMeasureCode
BOMLevel	smallint
PerAssemblyQty	decimal(8,2)			((1.00))
ModifiedDate	datetime			(getdate())

Indexes

Name	Type	Columns
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate	Clustered	ProductAssemblyID, ComponentID, StartDate
PK_BillOfMaterials_BillOfMaterialsID	NonClustered	BillOfMaterialsID
IX_BillOfMaterials_UnitMeasureCode	NonClustered	UnitMeasureCode

Check Constraints

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

Foreign Key Constraints

Name	Type	Referenced Table
FK_BillOfMaterials_Product_ComponentID	Foreign key	Production.Product
FK_BillOfMaterials_Product_ProductAssemblyID	Foreign key	Production.Product
FK_BillOfMaterials_UnitMeasure_UnitMeasureCode	Foreign key	Production.UnitMeasure

SQL Script

CREATE TABLE [Production].[BillOfMaterials] (
    [BillOfMaterialsID] int NOT NULL IDENTITY,
    [ProductAssemblyID] int NULL,
    [ComponentID] int NOT NULL,
    [StartDate] datetime NOT NULL DEFAULT (getdate()),
    [EndDate] datetime NULL,
    [UnitMeasureCode] nchar(3) NOT NULL,
    [BOMLevel] smallint NOT NULL,
    [PerAssemblyQty] decimal(8,2) NOT NULL DEFAULT ((1.00)),
    [ModifiedDate] datetime NOT NULL DEFAULT (getdate())
);
CREATE UNIQUE INDEX [AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate] on [Production].[BillOfMaterials]([ProductAssemblyID], [ComponentID], [StartDate]);
CREATE INDEX [IX_BillOfMaterials_UnitMeasureCode] on [Production].[BillOfMaterials]([UnitMeasureCode]);