Production.BillOfMaterials Table

Description

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

Properties

Creation Date	27/10/2017 14:33
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	2679
Data Space Used	160.00 KB
Index Space Used	200.00 KB

Columns

Column Name	Description	Datatype	Length	Default
BillOfMaterialsID	Primary key for BillOfMaterials records.	Integer	4
ProductAssemblyID	Parent product identification number. Foreign key to Product.ProductID.	Integer	4
ComponentID	Component identification number. Foreign key to Product.ProductID.	Integer	4
StartDate	Date the component started being used in the assembly item.	DBTimeStamp	4	(getdate())
EndDate	Date the component stopped being used in the assembly item.	DBTimeStamp	4
UnitMeasureCode	Standard code identifying the unit of measure for the quantity.	WChar	3
BOMLevel	Indicates the depth the component is from its parent (AssemblyID).	SmallInt	2
PerAssemblyQty	Quantity of the component needed to create the assembly.	Numeric	9 (8,2)	((1.00))
ModifiedDate	Date and time the record was last updated.	DBTimeStamp	4	(getdate())

Indexes

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

Check Constraints

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

Relationships

Relationship	Description
FK_BillOfMaterials_Product_ComponentID	Foreign key constraint referencing Product.ComponentID.
FK_BillOfMaterials_Product_ProductAssemblyID	Foreign key constraint referencing Product.ProductAssemblyID.
FK_BillOfMaterials_UnitMeasure_UnitMeasureCode	Foreign key constraint referencing UnitMeasure.UnitMeasureCode.

Objects that depend on Production.BillOfMaterials

	Database Object	Object Type	Description	Dep Level
	dbo.uspGetBillOfMaterials	Stored Procedure	Stored procedure using a recursive query to return a multi-level bill of material for the specified ProductID.	1
	dbo.uspGetWhereUsedProductID	Stored Procedure	Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID.	1

Objects that Production.BillOfMaterials depends on

Database Object	Object Type	Description	Dep Level
dbo.Flag	User Defined Data Type		2
dbo.Name	User Defined Data Type		2
Production.Product	Table	Products sold or used in the manfacturing of sold products.	1
Production.ProductCategory	Table	High-level product categorization.	3
Production.ProductModel	Table	Product model classification.	2
Production.ProductSubcategory	Table	Product subcategories. See ProductCategory table.	2
Production.UnitMeasure	Table	Unit of measure lookup table.	1

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[BillOfMaterials](
    [BillOfMaterialsID] [int] IDENTITY(1,1) NOT NULL,
    [ProductAssemblyID] [int] NULL,
    [ComponentID] [int] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NULL,
    [UnitMeasureCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [BOMLevel] [smallint] NOT NULL,
    [PerAssemblyQty] [decimal](8, 2) NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_BillOfMaterials_BillOfMaterialsID] PRIMARY KEY NONCLUSTERED 
(
    [BillOfMaterialsID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [Production].[BillOfMaterials] ADD  CONSTRAINT [DF_BillOfMaterials_StartDate]  DEFAULT (getdate()) FOR [StartDate]
ALTER TABLE [Production].[BillOfMaterials] ADD  CONSTRAINT [DF_BillOfMaterials_PerAssemblyQty]  DEFAULT ((1.00)) FOR [PerAssemblyQty]
ALTER TABLE [Production].[BillOfMaterials] ADD  CONSTRAINT [DF_BillOfMaterials_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [Production].[BillOfMaterials]  WITH CHECK ADD  CONSTRAINT [FK_BillOfMaterials_Product_ComponentID] FOREIGN KEY([ComponentID])
REFERENCES [Production].[Product] ([ProductID])
ALTER TABLE [Production].[BillOfMaterials] CHECK CONSTRAINT [FK_BillOfMaterials_Product_ComponentID]
ALTER TABLE [Production].[BillOfMaterials]  WITH CHECK ADD  CONSTRAINT [FK_BillOfMaterials_Product_ProductAssemblyID] FOREIGN KEY([ProductAssemblyID])
REFERENCES [Production].[Product] ([ProductID])
ALTER TABLE [Production].[BillOfMaterials] CHECK CONSTRAINT [FK_BillOfMaterials_Product_ProductAssemblyID]
ALTER TABLE [Production].[BillOfMaterials]  WITH CHECK ADD  CONSTRAINT [FK_BillOfMaterials_UnitMeasure_UnitMeasureCode] FOREIGN KEY([UnitMeasureCode])
REFERENCES [Production].[UnitMeasure] ([UnitMeasureCode])
ALTER TABLE [Production].[BillOfMaterials] CHECK CONSTRAINT [FK_BillOfMaterials_UnitMeasure_UnitMeasureCode]
ALTER TABLE [Production].[BillOfMaterials]  WITH CHECK ADD  CONSTRAINT [CK_BillOfMaterials_BOMLevel] CHECK  (([ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1)))
ALTER TABLE [Production].[BillOfMaterials] CHECK CONSTRAINT [CK_BillOfMaterials_BOMLevel]
ALTER TABLE [Production].[BillOfMaterials]  WITH CHECK ADD  CONSTRAINT [CK_BillOfMaterials_EndDate] CHECK  (([EndDate]>[StartDate] OR [EndDate] IS NULL))
ALTER TABLE [Production].[BillOfMaterials] CHECK CONSTRAINT [CK_BillOfMaterials_EndDate]
ALTER TABLE [Production].[BillOfMaterials]  WITH CHECK ADD  CONSTRAINT [CK_BillOfMaterials_PerAssemblyQty] CHECK  (([PerAssemblyQty]>=(1.00)))
ALTER TABLE [Production].[BillOfMaterials] CHECK CONSTRAINT [CK_BillOfMaterials_PerAssemblyQty]
ALTER TABLE [Production].[BillOfMaterials]  WITH CHECK ADD  CONSTRAINT [CK_BillOfMaterials_ProductAssemblyID] CHECK  (([ProductAssemblyID]<>[ComponentID]))
ALTER TABLE [Production].[BillOfMaterials] CHECK CONSTRAINT [CK_BillOfMaterials_ProductAssemblyID]

Related Objects