AdventureWorks
| Table Name: | Production.BillOfMaterials |
| Description: | Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components. |
| Primary Keys: | BillOfMaterialsID |
| Field | Type | Defaults | Nulls? | Comments |
|---|---|---|---|---|
| BillOfMaterialsID | int - Identity | None | No | Primary key for BillOfMaterials records. |
| ProductAssemblyID | int | None | Yes | Parent product identification number. Foreign key to Product.ProductID. |
| ComponentID | int | None | No | Component identification number. Foreign key to Product.ProductID. |
| StartDate | datetime | (getdate()) | No | Date the component started being used in the assembly item. |
| EndDate | datetime | None | Yes | Date the component stopped being used in the assembly item. |
| UnitMeasureCode | nchar (3) | None | No | Standard code identifying the unit of measure for the quantity. |
| BOMLevel | smallint | None | No | Indicates the depth the component is from its parent (AssemblyID). |
| PerAssemblyQty | decimal (8, 2) | ((1.00)) | No | Quantity of the component needed to create the assembly. |
| ModifiedDate | datetime | (getdate()) | No | Date and time the record was last updated. |
| Index | Clustered? | Unique? | Fields |
|---|---|---|---|
| AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate | Yes | Yes | ProductAssemblyID ComponentID StartDate |
| PK_BillOfMaterials_BillOfMaterialsID | No | Yes | BillOfMaterialsID |
| IX_BillOfMaterials_UnitMeasureCode | No | No | UnitMeasureCode |
| Check Constraint | Text |
|---|---|
| CK_BillOfMaterials_EndDate | ([EndDate]>[StartDate] OR [EndDate] IS NULL) |
| CK_BillOfMaterials_ProductAssemblyID | ([ProductAssemblyID]<>[ComponentID]) |
| CK_BillOfMaterials_BOMLevel | ([ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1)) |
| CK_BillOfMaterials_PerAssemblyQty | ([PerAssemblyQty]>=(1.00)) |
| Internal Foreign Key Constraint | Affected Field | Source Table |
|---|---|---|
| FK_BillOfMaterials_Product_ComponentID | ComponentID | Production.Product |
| FK_BillOfMaterials_Product_ProductAssemblyID | ProductAssemblyID | Production.Product |
| FK_BillOfMaterials_UnitMeasure_UnitMeasureCode | UnitMeasureCode | Production.UnitMeasure |