Description
Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.
Table properties
| name | value |
|---|
| name | [Production].[BillOfMaterials] |
| created | Mar 19 2009 9:09PM |
| modified | Mar 19 2009 9:10PM
|
| ansi nulls | on
|
| quoted identifier | on
|
| row count | 2,679 |
| Size of data | 160 kb |
| Size of indexes | 200 kb |
| Maximum size of a single row | 55 bytes |
Columns
| column | datatype | length | bytes | default | nulls | PK | FK | UQ | computed | comment |
|---|
| BillOfMaterialsID | int identity(1,1) | 10 | 4 | | no
| yes
| |
| no
| Primary key for BillOfMaterials records.
|
| ProductAssemblyID | int | 10 | 4 | | yes
|
| Product.ProductID
|
| no
| Parent product identification number. Foreign key to Product.ProductID.
|
| ComponentID | int | 10 | 4 | | no
|
| Product.ProductID
|
| no
| Component identification number. Foreign key to Product.ProductID.
|
| StartDate | datetime | 23 | 8 | (getdate()) | no
|
| |
| no
| Date the component started being used in the assembly item.
|
| EndDate | datetime | 23 | 8 | | yes
|
| |
| no
| Date the component stopped being used in the assembly item.
|
| UnitMeasureCode | nchar(3) | 3 | 12 | | no
|
| UnitMeasure.UnitMeasureCode
|
| no
| Standard code identifying the unit of measure for the quantity.
|
| BOMLevel | smallint | 5 | 2 | | no
|
| |
| no
| Indicates the depth the component is from its parent (AssemblyID).
|
| PerAssemblyQty | decimal(8,2) | 8 | 5 | ((1.00)) | no
|
| |
| no
| Quantity of the component needed to create the assembly.
|
| ModifiedDate | datetime | 23 | 8 | (getdate()) | no
|
| |
| no
| Date and time the record was last updated.
|
Indexes
References
Foreign key graph
Foreign keys
| name | columns | foreign columns | type | comment |
|---|
| FK_BillOfMaterials_Product_ProductAssemblyID | ProductAssemblyID | Product.ProductID |
| Foreign key constraint referencing Product.ProductAssemblyID.
|
| FK_BillOfMaterials_Product_ComponentID | ComponentID | Product.ProductID |
| Foreign key constraint referencing Product.ComponentID.
|
| FK_BillOfMaterials_UnitMeasure_UnitMeasureCode | UnitMeasureCode | UnitMeasure.UnitMeasureCode |
| Foreign key constraint referencing UnitMeasure.UnitMeasureCode.
|
Check constraints
| name | column | comment |
|---|
CK_BillOfMaterials_EndDate([EndDate]>[StartDate] OR [EndDate] IS NULL) | StartDate
EndDate
| Check constraint EndDate] > [StartDate] OR [EndDate] IS NULL
|
CK_BillOfMaterials_ProductAssemblyID([ProductAssemblyID]<>[ComponentID]) | ProductAssemblyID
ComponentID
| Check constraint [ProductAssemblyID] <> [ComponentID]
|
CK_BillOfMaterials_BOMLevel([ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1)) | ProductAssemblyID
BOMLevel
PerAssemblyQty
| Check constraint [ProductAssemblyID] IS NULL AND [BOMLevel] = (0) AND [PerAssemblyQty] = (1) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel] >= (1)
|
CK_BillOfMaterials_PerAssemblyQty([PerAssemblyQty]>=(1.00)) | PerAssemblyQty
| Check constraint [PerAssemblyQty] >= (1.00)
|
Defaults
Dependency graph
Objects that depend on [Production].[BillOfMaterials]
Sample rows
| BillOfMaterialsID | ProductAssemblyID | ComponentID | StartDate | EndDate | UnitMeasureCode | BOMLevel | PerAssemblyQty | ModifiedDate |
|---|
| 893
| NULL
| 749
| 6/26/2000 12:00:00 AM
| NULL
| EA
| 0
| 1.00
| 6/12/2000 12:00:00 AM
|
| 271
| NULL
| 750
| 4/4/2000 12:00:00 AM
| 6/3/2000 12:00:00 AM
| EA
| 0
| 1.00
| 6/3/2000 12:00:00 AM
|
| 34
| NULL
| 750
| 6/4/2000 12:00:00 AM
| NULL
| EA
| 0
| 1.00
| 5/21/2000 12:00:00 AM
|
| 830
| NULL
| 751
| 6/26/2000 12:00:00 AM
| NULL
| EA
| 0
| 1.00
| 6/12/2000 12:00:00 AM
|
| 2074
| NULL
| 752
| 8/8/2000 12:00:00 AM
| NULL
| EA
| 0
| 1.00
| 7/25/2000 12:00:00 AM
|
| 1950
| NULL
| 753
| 7/20/2000 12:00:00 AM
| 9/18/2000 12:00:00 AM
| EA
| 0
| 1.00
| 9/18/2000 12:00:00 AM
|
| 1761
| NULL
| 753
| 9/19/2000 12:00:00 AM
| NULL
| EA
| 0
| 1.00
| 9/5/2000 12:00:00 AM
|
| 3088
| NULL
| 754
| 1/15/2001 12:00:00 AM
| NULL
| EA
| 0
| 1.00
| 1/1/2001 12:00:00 AM
|
| 3351
| NULL
| 755
| 1/23/2001 12:00:00 AM
| NULL
| EA
| 0
| 1.00
| 1/9/2001 12:00:00 AM
|
| 3246
| NULL
| 756
| 1/23/2001 12:00:00 AM
| NULL
| EA
| 0
| 1.00
| 1/9/2001 12:00:00 AM
|
Code
CREATE TABLE [Production].[BillOfMaterials](
[BillOfMaterialsID] [int] IDENTITY(1,1) NOT NULL,
[ProductAssemblyID] [int] NULL,
[ComponentID] [int] NOT NULL,
[StartDate] [datetime] NOT NULL,
[BillOfMaterialsID] [int] IDENTITY(1,1) 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] 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]
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]