Properties

Owner:	Production
Creation Date:	04/26/2006
Located On:	PRIMARY
Data Size KB:	160
Index Size KB:	200
Rows:	2679
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	Length	NULL	Default	IsIdentity	IsGUID	Description
		BillOfMaterialsID	int	4					Primary key for BillOfMaterials records.
		ProductAssemblyID	int	4					Parent product identification number. Foreign key to Product.ProductID.
		ComponentID	int	4					Component identification number. Foreign key to Product.ProductID.
		StartDate	datetime	8		(getdate())			Date the component started being used in the assembly item.
		EndDate	datetime	8					Date the component stopped being used in the assembly item.
		UnitMeasureCode	nchar	3					Standard code identifying the unit of measure for the quantity.
		BOMLevel	smallint	2					Indicates the depth the component is from its parent (AssemblyID).
		PerAssemblyQty	decimal	5		((1.00))			Quantity of the component needed to create the assembly.
		ModifiedDate	datetime	8		(getdate())			Date and time the record was last updated.

Total: 9 column(s)

Identity column

	Name	Seed	Increment	Not for replication
	BillOfMaterialsID	1	1

Indexes

	Index	Primary	Unique	Description
	AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate			Clustered index.
	PK_BillOfMaterials_BillOfMaterialsID			Primary key (clustered) constraint
	IX_BillOfMaterials_UnitMeasureCode			Nonclustered index.

Total: 3 index(es)

Check Constraints

	Name	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_PerAssemblyQty	([PerAssemblyQty]>=(1.00))
	CK_BillOfMaterials_ProductAssemblyID	([ProductAssemblyID]<>[ComponentID])

Total: 4 constraint(s)

Referenced Tables

	Table	Foreign Key	Primary Key or Unique Constraint
	Production.Product	FK_BillOfMaterials_Product_ProductAssemblyID	PK_Product_ProductID
	Production.Product	FK_BillOfMaterials_Product_ComponentID	PK_Product_ProductID
	Production.UnitMeasure	FK_BillOfMaterials_UnitMeasure_UnitMeasureCode	PK_UnitMeasure_UnitMeasureCode

Total: 3 table(s)

Objects that [Production].[BillOfMaterials] depends on

	Object Name	Owner	Object Type	Dep Level
	Flag	dbo	User Defined type	1
	Name	dbo	User Defined type	1
	ProductCategory	Production	Table	2
	ProductModel	Production	Table	2
	UnitMeasure	Production	Table	2
	ProductSubcategory	Production	Table	3
	Product	Production	Table	4

Total: 7 object(s)

Objects that depend on [Production].[BillOfMaterials]

	Object Name	Owner	Object Type	Dep Level
	uspGetBillOfMaterials	dbo	Procedure	1
	uspGetWhereUsedProductID	dbo	Procedure	1

Total: 2 object(s)

SQL

CREATE TABLE [BillOfMaterials] (
    [BillOfMaterialsID] [int] IDENTITY (1, 1) NOT NULL ,
    [ProductAssemblyID] [int] NULL ,
    [ComponentID] [int] NOT NULL ,
    [StartDate] [datetime] NOT NULL CONSTRAINT [DF_BillOfMaterials_StartDate] DEFAULT (getdate()),
    [EndDate] [datetime] NULL ,
    [UnitMeasureCode] [nchar] (3) COLLATE Latin1_General_CS_AS NOT NULL ,
    [BOMLevel] [smallint] NOT NULL ,
    [PerAssemblyQty] [decimal](8, 2) NOT NULL CONSTRAINT [DF_BillOfMaterials_PerAssemblyQty] DEFAULT ((1.00)),
    [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_BillOfMaterials_ModifiedDate] DEFAULT (getdate()),
    CONSTRAINT [PK_BillOfMaterials_BillOfMaterialsID] PRIMARY KEY  NONCLUSTERED
    (
        [BillOfMaterialsID]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_BillOfMaterials_Product_ComponentID] FOREIGN KEY
    (
        [ComponentID]
    ) REFERENCES [Product] (
        [ProductID]
    ),
    CONSTRAINT [FK_BillOfMaterials_Product_ProductAssemblyID] FOREIGN KEY
    (
        [ProductAssemblyID]
    ) REFERENCES [Product] (
        [ProductID]
    ),
    CONSTRAINT [FK_BillOfMaterials_UnitMeasure_UnitMeasureCode] FOREIGN KEY
    (
        [UnitMeasureCode]
    ) REFERENCES [UnitMeasure] (
        [UnitMeasureCode]
    ),
    CONSTRAINT [CK_BillOfMaterials_BOMLevel] CHECK ([ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1)),
    CONSTRAINT [CK_BillOfMaterials_EndDate] CHECK ([EndDate]>[StartDate] OR [EndDate] IS NULL),
    CONSTRAINT [CK_BillOfMaterials_PerAssemblyQty] CHECK ([PerAssemblyQty]>=(1.00)),
    CONSTRAINT [CK_BillOfMaterials_ProductAssemblyID] CHECK ([ProductAssemblyID]<>[ComponentID])
) ON [PRIMARY]
GO

See Also

List of tables