Difference between revisions of "Production.BillOfMaterials (table)"

From dbscript Online Help
Jump to: navigation, search
 
(One intermediate revision by the same user not shown)
Line 2: Line 2:
  
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
−
|-
+
|- valign="top"
 
| '''Table
 
| '''Table
 
| Production.BillOfMaterials
 
| Production.BillOfMaterials
Line 8: Line 8:
 
| '''Description
 
| '''Description
 
| Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.
 
| Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.
−
|-
 
 
|}
 
|}
  
−
 
+
=== Columns ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 18: Line 17:
 
| '''Nullable
 
| '''Nullable
 
| '''Default
 
| '''Default
−
| '''Description / PK / Index
+
| '''Description
−
+
 
 
|- valign="top"
 
|- valign="top"
 
| BillOfMaterialsID
 
| BillOfMaterialsID
Line 25: Line 24:
 
| not null
 
| not null
 
|  
 
|  
−
| Primary key for BillOfMaterials records.<br />PK_BillOfMaterials_BillOfMaterialsID
+
| Primary key for BillOfMaterials records.
 
|- valign="top"
 
|- valign="top"
 
| ProductAssemblyID
 
| ProductAssemblyID
Line 31: Line 30:
 
| null
 
| null
 
|  
 
|  
−
| Parent product identification number. Foreign key to Product.ProductID.<br />AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
+
| Parent product identification number. Foreign key to Product.ProductID.
 
|- valign="top"
 
|- valign="top"
 
| ComponentID
 
| ComponentID
Line 37: Line 36:
 
| not null
 
| not null
 
|  
 
|  
−
| Component identification number. Foreign key to Product.ProductID.<br />AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
+
| Component identification number. Foreign key to Product.ProductID.
 
|- valign="top"
 
|- valign="top"
 
| StartDate
 
| StartDate
Line 43: Line 42:
 
| not null
 
| not null
 
| (getdate())
 
| (getdate())
−
| Date the component started being used in the assembly item.<br />AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
+
| Date the component started being used in the assembly item.
 
|- valign="top"
 
|- valign="top"
 
| EndDate
 
| EndDate
Line 49: Line 48:
 
| null
 
| null
 
|  
 
|  
−
| Date the component stopped being used in the assembly item.<br />
+
| Date the component stopped being used in the assembly item.
 
|- valign="top"
 
|- valign="top"
 
| UnitMeasureCode
 
| UnitMeasureCode
Line 55: Line 54:
 
| not null
 
| not null
 
|  
 
|  
−
| Standard code identifying the unit of measure for the quantity.<br />IX_BillOfMaterials_UnitMeasureCode
+
| Standard code identifying the unit of measure for the quantity.
 
|- valign="top"
 
|- valign="top"
 
| BOMLevel
 
| BOMLevel
Line 61: Line 60:
 
| not null
 
| not null
 
|  
 
|  
−
| Indicates the depth the component is from its parent (AssemblyID).<br />
+
| Indicates the depth the component is from its parent (AssemblyID).
 
|- valign="top"
 
|- valign="top"
 
| PerAssemblyQty
 
| PerAssemblyQty
Line 67: Line 66:
 
| not null
 
| not null
 
| ((1.00))
 
| ((1.00))
−
| Quantity of the component needed to create the assembly.<br />
+
| Quantity of the component needed to create the assembly.
 
|- valign="top"
 
|- valign="top"
 
| ModifiedDate
 
| ModifiedDate
Line 73: Line 72:
 
| not null
 
| not null
 
| (getdate())
 
| (getdate())
−
| Date and time the record was last updated.<br />
+
| Date and time the record was last updated.
 
|}
 
|}
  
 +
=== Primary Key ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Primary Key
 +
| '''Columns
 +
|- valign="top"
 +
| PK_BillOfMaterials_BillOfMaterialsID
 +
| BillOfMaterialsID
 +
|}
  
 +
=== Indexes ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Index
 +
| '''Type
 +
| '''Columns
  
 +
|- valign="top"
 +
| AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
 +
| Unique
 +
| ProductAssemblyID, ComponentID, StartDate
 +
|- valign="top"
 +
| IX_BillOfMaterials_UnitMeasureCode
 +
|
 +
| UnitMeasureCode
 +
|}
 +
 +
=== Check Constraints ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Check Constraint
 +
| '''Expression
 +
| '''Description
 +
 +
|- valign="top"
 +
| CK_BillOfMaterials_BOMLevel
 +
| ([ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1))
 +
| Check constraint [ProductAssemblyID] IS NULL AND [BOMLevel] = (0) AND [PerAssemblyQty] = (1) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel] >= (1)
 +
|- valign="top"
 +
| CK_BillOfMaterials_EndDate
 +
| ([EndDate]>[StartDate] OR [EndDate] IS NULL)
 +
| Check constraint EndDate] > [StartDate] OR [EndDate] IS NULL
 +
|- valign="top"
 +
| CK_BillOfMaterials_PerAssemblyQty
 +
| ([PerAssemblyQty]>=(1.00))
 +
| Check constraint [PerAssemblyQty] >= (1.00)
 +
|- valign="top"
 +
| CK_BillOfMaterials_ProductAssemblyID
 +
| ([ProductAssemblyID]<>[ComponentID])
 +
| Check constraint [ProductAssemblyID] <> [ComponentID]
 +
|}
 +
 +
=== Foreign Keys ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 83: Line 133:
 
| '''Column
 
| '''Column
 
| '''Referenced Column
 
| '''Referenced Column
−
+
 
−
|-
+
|- valign="top"
 
| [[Production.Product_(table)|Production.Product]]
 
| [[Production.Product_(table)|Production.Product]]
 
| ComponentID
 
| ComponentID
 
| ProductID
 
| ProductID
−
|-
+
|- valign="top"
 
| [[Production.Product_(table)|Production.Product]]
 
| [[Production.Product_(table)|Production.Product]]
 
| ProductAssemblyID
 
| ProductAssemblyID
 
| ProductID
 
| ProductID
−
|-
+
|- valign="top"
 
| [[Production.UnitMeasure_(table)|Production.UnitMeasure]]
 
| [[Production.UnitMeasure_(table)|Production.UnitMeasure]]
 
| UnitMeasureCode
 
| UnitMeasureCode
Line 98: Line 148:
 
|}
 
|}
  
 +
=== References ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Dependency Type
 +
| '''Object Type
 +
| '''Referenced Object
 +
 +
|- valign="top"
 +
| Schema
 +
| Schema
 +
| [[Production_(schema)|Production]]
 +
 +
|}
  
 +
=== Dependencies ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
|- style="background:silver"
Line 105: Line 169:
 
| '''Referencing Object
 
| '''Referencing Object
  
−
|-
+
|- valign="top"
 
| Select
 
| Select
 
| Procedure
 
| Procedure
 
| [[dbo.uspGetBillOfMaterials_(procedure)|dbo.uspGetBillOfMaterials]]
 
| [[dbo.uspGetBillOfMaterials_(procedure)|dbo.uspGetBillOfMaterials]]
  
−
|-
+
|- valign="top"
 
| Select
 
| Select
 
| Procedure
 
| Procedure
Line 116: Line 180:
  
 
|}
 
|}
−
 
  
  

Latest revision as of 00:16, 24 June 2010

wikibot[edit]

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

Columns[edit]

Column Data Type Nullable Default Description
BillOfMaterialsID int not null Primary key for BillOfMaterials records.
ProductAssemblyID int null Parent product identification number. Foreign key to Product.ProductID.
ComponentID int not null Component identification number. Foreign key to Product.ProductID.
StartDate datetime not null (getdate()) Date the component started being used in the assembly item.
EndDate datetime null Date the component stopped being used in the assembly item.
UnitMeasureCode nchar(3) not null Standard code identifying the unit of measure for the quantity.
BOMLevel smallint not null Indicates the depth the component is from its parent (AssemblyID).
PerAssemblyQty decimal(8, 2) not null ((1.00)) Quantity of the component needed to create the assembly.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.

Primary Key[edit]

Primary Key Columns
PK_BillOfMaterials_BillOfMaterialsID BillOfMaterialsID

Indexes[edit]

Index Type Columns
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate Unique ProductAssemblyID, ComponentID, StartDate
IX_BillOfMaterials_UnitMeasureCode UnitMeasureCode

Check Constraints[edit]

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

Foreign Keys[edit]

Relation Column Referenced Column
Production.Product ComponentID ProductID
Production.Product ProductAssemblyID ProductID
Production.UnitMeasure UnitMeasureCode UnitMeasureCode

References[edit]

Dependency Type Object Type Referenced Object
Schema Schema Production

Dependencies[edit]

Reference Type Object Type Referencing Object
Select Procedure dbo.uspGetBillOfMaterials
Select Procedure dbo.uspGetWhereUsedProductID


automatically generated[edit]

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


Column Data Type Nullable Default Description / PK / Index
BillOfMaterialsID int not null Primary key for BillOfMaterials records.
PK_BillOfMaterials_BillOfMaterialsID
ProductAssemblyID int null Parent product identification number. Foreign key to Product.ProductID.
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
ComponentID int not null Component identification number. Foreign key to Product.ProductID.
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
StartDate datetime not null (GETDATE()) Date the component started being used in the assembly item.
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
EndDate datetime null Date the component stopped being used in the assembly item.
UnitMeasureCode nchar(3) not null Standard code identifying the unit of measure for the quantity.
IX_BillOfMaterials_UnitMeasureCode
BOMLevel smallint not null Indicates the depth the component is from its parent (AssemblyID).
PerAssemblyQty decimal(8, 2) not null (1.00) Quantity of the component needed to create the assembly.
ModifiedDate datetime not null (GETDATE()) Date and time the record was last updated.


Relation Column Referenced Column
Production.Product ComponentID ProductID
Production.Product ProductAssemblyID ProductID
Production.UnitMeasure UnitMeasureCode UnitMeasureCode


Dependency Type Object Type Referenced Object
Schema Schema Production


Reference Type Object Type Referencing Object
Select Procedure dbo.uspGetBillOfMaterials
Select Procedure dbo.uspGetWhereUsedProductID
Retrieved from "http://dbshelp.devio.at/index.php?title=Production.BillOfMaterials_(table)&oldid=2621"