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. 


Name Data Type Length NULL Default IsIdentity IsGUID Description
    BillOfMaterialsID  int          Primary key for BillOfMaterials records. 
    ProductAssemblyID  int          Parent product identification number. Foreign key to Product.ProductID. 
    ComponentID  int          Component identification number. Foreign key to Product.ProductID. 
    StartDate  datetime    (getdate())      Date the component started being used in the assembly item. 
    EndDate  datetime          Date the component stopped being used in the assembly item. 
    UnitMeasureCode  nchar          Standard code identifying the unit of measure for the quantity. 
    BOMLevel  smallint          Indicates the depth the component is from its parent (AssemblyID). 
    PerAssemblyQty  decimal    ((1.00))      Quantity of the component needed to create the assembly. 
    ModifiedDate  datetime    (getdate())      Date and time the record was last updated. 
Total: 9 column(s)

Identity column

Name Seed Increment Not for replication


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 
  Name  dbo  User Defined type 
  ProductCategory  Production  Table 
  ProductModel  Production  Table 
  UnitMeasure  Production  Table 
  ProductSubcategory  Production  Table 
  Product  Production  Table 
Total: 7 object(s)

Objects that depend on [Production].[BillOfMaterials]

Object Name Owner Object Type Dep Level
  uspGetBillOfMaterials  dbo  Procedure 
  uspGetWhereUsedProductID  dbo  Procedure 
Total: 2 object(s)


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()),
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_BillOfMaterials_Product_ComponentID] FOREIGN KEY
    ) REFERENCES [Product] (
    CONSTRAINT [FK_BillOfMaterials_Product_ProductAssemblyID] FOREIGN KEY
    ) REFERENCES [Product] (
    CONSTRAINT [FK_BillOfMaterials_UnitMeasure_UnitMeasureCode] FOREIGN KEY
    ) REFERENCES [UnitMeasure] (
    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])

See Also

List of tables