Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.
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 |
Column Name | Description | Datatype | Length | Allow Nulls | Default | Formula | |
---|---|---|---|---|---|---|---|
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()) |
Index | Description | Primary | Unique |
---|---|---|---|
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate | Clustered index. | ||
IX_BillOfMaterials_UnitMeasureCode | Nonclustered index. | ||
PK_BillOfMaterials_BillOfMaterialsID | Primary key (clustered) constraint |
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]) |
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. |
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 |
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 |
|