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

From dbscript Online Help
Jump to: navigation, search
Line 12: Line 12:
  
  
 +
=== 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 19:
 
| '''Nullable
 
| '''Nullable
 
| '''Default
 
| '''Default
−
| '''Description / PK / Index
+
| '''Description
 
 
 
|- valign="top"
 
|- valign="top"
Line 25: Line 26:
 
| 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 32:
 
| 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 38:
 
| 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 44:
 
| 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 50:
 
| 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 56:
 
| 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 62:
 
| 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 68:
 
| 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 74:
 
| 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
 +
|-
 +
| PK_BillOfMaterials_BillOfMaterialsID
 +
| BillOfMaterialsID
 +
|}
  
 +
=== Indexes ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Index
 +
| '''Type
 +
| '''Columns
 +
 +
|-
 +
| AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
 +
| Unique
 +
| ProductAssemblyID, ComponentID, StartDate
 +
|-
 +
| IX_BillOfMaterials_UnitMeasureCode
 +
|
 +
| UnitMeasureCode
 +
|}
 +
 +
=== Check Constraints ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''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 ===
 
{| 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 98: Line 150:
 
|}
 
|}
  
 +
=== References ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Dependency Type
 +
| '''Object Type
 +
| '''Referenced Object
 +
 +
|-
 +
| 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 116: Line 182:
  
 
|}
 
|}
−
 
  
  

Revision as of 23:05, 2 February 2010

wikibot

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

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

Primary Key Columns
PK_BillOfMaterials_BillOfMaterialsID BillOfMaterialsID

Indexes

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

Check Constraints

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

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

References

Dependency Type Object Type Referenced Object
Schema Schema Production

Dependencies

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


automatically generated

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=2100"