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

From dbscript Online Help
Jump to: navigation, search
 
(9 intermediate revisions 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.Product
 
| Production.Product
−
|-
+
|- valign="top"
 +
| '''Description
 +
| Products sold or used in the manfacturing of sold products.
 
|}
 
|}
  
−
 
+
=== 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 15: Line 17:
 
| '''Nullable
 
| '''Nullable
 
| '''Default
 
| '''Default
−
| '''PK
+
| '''Description
−
| '''Index
+
 
−
+
|- valign="top"
−
|-
 
 
| ProductID
 
| ProductID
 
| int
 
| int
 
| not null
 
| not null
 
|  
 
|  
−
| PK_Product_ProductID
+
| Primary key for Product records.
−
|
+
|- valign="top"
−
|-
 
 
| Name
 
| Name
 
| dbo.Name
 
| dbo.Name
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Name of the product.
−
| AK_Product_Name
+
|- valign="top"
−
|-
 
 
| ProductNumber
 
| ProductNumber
 
| nvarchar(25)
 
| nvarchar(25)
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Unique product identification number.
−
| AK_Product_ProductNumber
+
|- valign="top"
−
|-
 
 
| MakeFlag
 
| MakeFlag
 
| dbo.Flag
 
| dbo.Flag
 
| not null
 
| not null
 
| ((1))
 
| ((1))
−
|  
+
| 0 = Product is purchased, 1 = Product is manufactured in-house.
−
|
+
|- valign="top"
−
|-
 
 
| FinishedGoodsFlag
 
| FinishedGoodsFlag
 
| dbo.Flag
 
| dbo.Flag
 
| not null
 
| not null
 
| ((1))
 
| ((1))
−
|  
+
| 0 = Product is not a salable item. 1 = Product is salable.
−
|
+
|- valign="top"
−
|-
 
 
| Color
 
| Color
 
| nvarchar(15)
 
| nvarchar(15)
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Product color.
−
|
+
|- valign="top"
−
|-
 
 
| SafetyStockLevel
 
| SafetyStockLevel
 
| smallint
 
| smallint
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Minimum inventory quantity.
−
|
+
|- valign="top"
−
|-
 
 
| ReorderPoint
 
| ReorderPoint
 
| smallint
 
| smallint
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Inventory level that triggers a purchase order or work order.
−
|
+
|- valign="top"
−
|-
 
 
| StandardCost
 
| StandardCost
 
| money
 
| money
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Standard cost of the product.
−
|
+
|- valign="top"
−
|-
 
 
| ListPrice
 
| ListPrice
 
| money
 
| money
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Selling price.
−
|
+
|- valign="top"
−
|-
 
 
| Size
 
| Size
 
| nvarchar(5)
 
| nvarchar(5)
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Product size.
−
|
+
|- valign="top"
−
|-
 
 
| SizeUnitMeasureCode
 
| SizeUnitMeasureCode
 
| nchar(3)
 
| nchar(3)
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Unit of measure for Size column.
−
|
+
|- valign="top"
−
|-
 
 
| WeightUnitMeasureCode
 
| WeightUnitMeasureCode
 
| nchar(3)
 
| nchar(3)
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Unit of measure for Weight column.
−
|
+
|- valign="top"
−
|-
 
 
| Weight
 
| Weight
 
| decimal(8, 2)
 
| decimal(8, 2)
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Product weight.
−
|
+
|- valign="top"
−
|-
 
 
| DaysToManufacture
 
| DaysToManufacture
 
| int
 
| int
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Number of days required to manufacture the product.
−
|
+
|- valign="top"
−
|-
 
 
| ProductLine
 
| ProductLine
 
| nchar(2)
 
| nchar(2)
 
| null
 
| null
 
|  
 
|  
−
|  
+
| R = Road, M = Mountain, T = Touring, S = Standard
−
|
+
|- valign="top"
−
|-
 
 
| Class
 
| Class
 
| nchar(2)
 
| nchar(2)
 
| null
 
| null
 
|  
 
|  
−
|  
+
| H = High, M = Medium, L = Low
−
|
+
|- valign="top"
−
|-
 
 
| Style
 
| Style
 
| nchar(2)
 
| nchar(2)
 
| null
 
| null
 
|  
 
|  
−
|  
+
| W = Womens, M = Mens, U = Universal
−
|
+
|- valign="top"
−
|-
 
 
| ProductSubcategoryID
 
| ProductSubcategoryID
 
| int
 
| int
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Product is a member of this product subcategory. Foreign key to ProductSubCategory.ProductSubCategoryID.
−
|
+
|- valign="top"
−
|-
 
 
| ProductModelID
 
| ProductModelID
 
| int
 
| int
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Product is a member of this product model. Foreign key to ProductModel.ProductModelID.
−
|
+
|- valign="top"
−
|-
 
 
| SellStartDate
 
| SellStartDate
 
| datetime
 
| datetime
 
| not null
 
| not null
 
|  
 
|  
−
|  
+
| Date the product was available for sale.
−
|
+
|- valign="top"
−
|-
 
 
| SellEndDate
 
| SellEndDate
 
| datetime
 
| datetime
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Date the product was no longer available for sale.
−
|
+
|- valign="top"
−
|-
 
 
| DiscontinuedDate
 
| DiscontinuedDate
 
| datetime
 
| datetime
 
| null
 
| null
 
|  
 
|  
−
|  
+
| Date the product was discontinued.
−
|
+
|- valign="top"
−
|-
 
 
| rowguid
 
| rowguid
 
| uniqueidentifier
 
| uniqueidentifier
 
| not null
 
| not null
 
| (newid())
 
| (newid())
−
|  
+
| ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
−
| AK_Product_rowguid
+
|- valign="top"
−
|-
 
 
| ModifiedDate
 
| ModifiedDate
 
| datetime
 
| datetime
 
| not null
 
| not null
 
| (getdate())
 
| (getdate())
−
|  
+
| 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_Product_ProductID
 +
| ProductID
 +
|}
 +
 
 +
=== Indexes ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Index
 +
| '''Type
 +
| '''Columns
 +
 
 +
|- valign="top"
 +
| AK_Product_Name
 +
| Unique
 +
| Name
 +
|- valign="top"
 +
| AK_Product_ProductNumber
 +
| Unique
 +
| ProductNumber
 +
|- valign="top"
 +
| AK_Product_rowguid
 +
| Unique
 +
| rowguid
 
|}
 
|}
  
 +
=== Check Constraints ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Check Constraint
 +
| '''Expression
 +
| '''Description
  
 +
|- valign="top"
 +
| CK_Product_Class
 +
| (upper([Class])='H' OR upper([Class])='M' OR upper([Class])='L' OR [Class] IS NULL)
 +
| Check constraint [Class]='h' OR [Class]='m' OR [Class]='l' OR [Class]='H' OR [Class]='M' OR [Class]='L' OR [Class] IS NULL
 +
|- valign="top"
 +
| CK_Product_DaysToManufacture
 +
| ([DaysToManufacture]>=(0))
 +
| Check constraint [DaysToManufacture] >= (0)
 +
|- valign="top"
 +
| CK_Product_ListPrice
 +
| ([ListPrice]>=(0.00))
 +
| Check constraint [ListPrice] >= (0.00)
 +
|- valign="top"
 +
| CK_Product_ProductLine
 +
| (upper([ProductLine])='R' OR upper([ProductLine])='M' OR upper([ProductLine])='T' OR upper([ProductLine])='S' OR [ProductLine] IS NULL)
 +
| Check constraint [ProductLine]='r' OR [ProductLine]='m' OR [ProductLine]='t' OR [ProductLine]='s' OR [ProductLine]='R' OR [ProductLine]='M' OR [ProductLine]='T' OR [ProductLine]='S' OR [ProductLine] IS NULL
 +
|- valign="top"
 +
| CK_Product_ReorderPoint
 +
| ([ReorderPoint]>(0))
 +
| Check constraint [ReorderPoint] > (0)
 +
|- valign="top"
 +
| CK_Product_SafetyStockLevel
 +
| ([SafetyStockLevel]>(0))
 +
| Check constraint [SafetyStockLevel] > (0)
 +
|- valign="top"
 +
| CK_Product_SellEndDate
 +
| ([SellEndDate]>=[SellStartDate] OR [SellEndDate] IS NULL)
 +
| Check constraint [SellEndDate] >= [SellStartDate] OR [SellEndDate] IS NULL
 +
|- valign="top"
 +
| CK_Product_StandardCost
 +
| ([StandardCost]>=(0.00))
 +
| Check constraint [SafetyStockLevel] > (0)
 +
|- valign="top"
 +
| CK_Product_Style
 +
| (upper([Style])='U' OR upper([Style])='M' OR upper([Style])='W' OR [Style] IS NULL)
 +
| Check constraint [Style]='u' OR [Style]='m' OR [Style]='w' OR [Style]='U' OR [Style]='M' OR [Style]='W' OR [Style] IS NULL
 +
|- valign="top"
 +
| CK_Product_Weight
 +
| ([Weight]>(0.00))
 +
| Check constraint [Weight] > (0.00)
 +
|}
  
 +
=== 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 202: Line 257:
 
| '''Column
 
| '''Column
 
| '''Referenced Column
 
| '''Referenced Column
−
+
 
−
|-
+
|- valign="top"
 
| [[Production.ProductModel_(table)|Production.ProductModel]]
 
| [[Production.ProductModel_(table)|Production.ProductModel]]
 
| ProductModelID
 
| ProductModelID
 
| ProductModelID
 
| ProductModelID
−
|-
+
|- valign="top"
 
| [[Production.ProductSubcategory_(table)|Production.ProductSubcategory]]
 
| [[Production.ProductSubcategory_(table)|Production.ProductSubcategory]]
 
| ProductSubcategoryID
 
| ProductSubcategoryID
 
| ProductSubcategoryID
 
| ProductSubcategoryID
−
|-
+
|- valign="top"
 
| [[Production.UnitMeasure_(table)|Production.UnitMeasure]]
 
| [[Production.UnitMeasure_(table)|Production.UnitMeasure]]
 
| SizeUnitMeasureCode
 
| SizeUnitMeasureCode
 
| UnitMeasureCode
 
| UnitMeasureCode
−
|-
+
|- valign="top"
 
| [[Production.UnitMeasure_(table)|Production.UnitMeasure]]
 
| [[Production.UnitMeasure_(table)|Production.UnitMeasure]]
 
| WeightUnitMeasureCode
 
| WeightUnitMeasureCode
Line 221: Line 276:
 
|}
 
|}
  
−
 
+
=== Detail Tables ===
 
{| 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 227: Line 282:
 
| '''Column
 
| '''Column
 
| '''Referencing Column
 
| '''Referencing Column
−
+
 
−
|-
+
|- valign="top"
 
| [[Production.BillOfMaterials_(table)|Production.BillOfMaterials]]
 
| [[Production.BillOfMaterials_(table)|Production.BillOfMaterials]]
 
| ProductID
 
| ProductID
 
| ComponentID
 
| ComponentID
−
|-
+
|- valign="top"
 
| [[Production.BillOfMaterials_(table)|Production.BillOfMaterials]]
 
| [[Production.BillOfMaterials_(table)|Production.BillOfMaterials]]
 
| ProductID
 
| ProductID
 
| ProductAssemblyID
 
| ProductAssemblyID
−
|-
+
|- valign="top"
 
| [[Production.ProductCostHistory_(table)|Production.ProductCostHistory]]
 
| [[Production.ProductCostHistory_(table)|Production.ProductCostHistory]]
 
| ProductID
 
| ProductID
 
| ProductID
 
| ProductID
−
|-
+
|- valign="top"
 
| [[Production.ProductDocument_(table)|Production.ProductDocument]]
 
| [[Production.ProductDocument_(table)|Production.ProductDocument]]
 
| ProductID
 
| ProductID
 
| ProductID
 
| ProductID
−
|-
+
|- valign="top"
 
| [[Production.ProductInventory_(table)|Production.ProductInventory]]
 
| [[Production.ProductInventory_(table)|Production.ProductInventory]]
 
| ProductID
 
| ProductID
 
| ProductID
 
| ProductID
−
|-
+
|- valign="top"
 
| [[Production.ProductListPriceHistory_(table)|Production.ProductListPriceHistory]]
 
| [[Production.ProductListPriceHistory_(table)|Production.ProductListPriceHistory]]
 
| ProductID
 
| ProductID
 
| ProductID
 
| ProductID
−
|-
+
|- valign="top"
 
| [[Production.ProductProductPhoto_(table)|Production.ProductProductPhoto]]
 
| [[Production.ProductProductPhoto_(table)|Production.ProductProductPhoto]]
 
| ProductID
 
| ProductID
 
| ProductID
 
| ProductID
−
|-
+
|- valign="top"
 
| [[Production.ProductReview_(table)|Production.ProductReview]]
 
| [[Production.ProductReview_(table)|Production.ProductReview]]
 
| ProductID
 
| ProductID
 
| ProductID
 
| ProductID
−
|-
+
|- valign="top"
 
| [[Purchasing.ProductVendor_(table)|Purchasing.ProductVendor]]
 
| [[Purchasing.ProductVendor_(table)|Purchasing.ProductVendor]]
 
| ProductID
 
| ProductID
 
| ProductID
 
| ProductID
−
|-
+
|- valign="top"
 
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]
 
| [[Purchasing.PurchaseOrderDetail_(table)|Purchasing.PurchaseOrderDetail]]
 
| ProductID
 
| ProductID
 
| ProductID
 
| ProductID
−
|-
+
|- valign="top"
 
| [[Sales.ShoppingCartItem_(table)|Sales.ShoppingCartItem]]
 
| [[Sales.ShoppingCartItem_(table)|Sales.ShoppingCartItem]]
 
| ProductID
 
| ProductID
 
| ProductID
 
| ProductID
−
|-
+
|- valign="top"
 
| [[Sales.SpecialOfferProduct_(table)|Sales.SpecialOfferProduct]]
 
| [[Sales.SpecialOfferProduct_(table)|Sales.SpecialOfferProduct]]
 
| ProductID
 
| ProductID
 
| ProductID
 
| ProductID
−
|-
+
|- valign="top"
 
| [[Production.TransactionHistory_(table)|Production.TransactionHistory]]
 
| [[Production.TransactionHistory_(table)|Production.TransactionHistory]]
 
| ProductID
 
| ProductID
 
| ProductID
 
| ProductID
−
|-
+
|- valign="top"
 
| [[Production.WorkOrder_(table)|Production.WorkOrder]]
 
| [[Production.WorkOrder_(table)|Production.WorkOrder]]
 
| ProductID
 
| ProductID
 
| ProductID
 
| ProductID
 
|}
 
|}
 +
 +
=== References ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Dependency Type
 +
| '''Object Type
 +
| '''Referenced Object
 +
 +
|- valign="top"
 +
| Data Type
 +
| Type
 +
| [[dbo.Flag_(type)|dbo.Flag]]
 +
 +
|- valign="top"
 +
| Data Type
 +
| Type
 +
| [[dbo.Name_(type)|dbo.Name]]
 +
 +
|- valign="top"
 +
| Schema
 +
| Schema
 +
| [[Production_(schema)|Production]]
 +
 +
|}
 +
 +
=== Dependencies ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Reference Type
 +
| '''Object Type
 +
| '''Referencing Object
 +
 +
|- valign="top"
 +
| Select
 +
| View
 +
| [[Production.vProductAndDescription_(view)|Production.vProductAndDescription]]
 +
 +
|- valign="top"
 +
| Select
 +
| Procedure
 +
| [[dbo.uspGetBillOfMaterials_(procedure)|dbo.uspGetBillOfMaterials]]
 +
 +
|- valign="top"
 +
| Select
 +
| Procedure
 +
| [[dbo.uspGetWhereUsedProductID_(procedure)|dbo.uspGetWhereUsedProductID]]
 +
 +
|- valign="top"
 +
| Select
 +
| Function
 +
| [[dbo.ufnGetProductDealerPrice_(function)|dbo.ufnGetProductDealerPrice]]
 +
 +
|- valign="top"
 +
| Select
 +
| Function
 +
| [[dbo.ufnGetProductListPrice_(function)|dbo.ufnGetProductListPrice]]
 +
 +
|- valign="top"
 +
| Select
 +
| Function
 +
| [[dbo.ufnGetProductStandardCost_(function)|dbo.ufnGetProductStandardCost]]
 +
 +
|}
 +
 +
 
== automatically generated ==
 
== automatically generated ==
  
Line 291: Line 411:
 
| '''Table
 
| '''Table
 
| Production.Product
 
| Production.Product
−
|-
+
|- valign="top"
−
 
 
 
| '''Description
 
| '''Description
 
| Products sold or used in the manfacturing of sold products.
 
| Products sold or used in the manfacturing of sold products.
 
|-
 
|-
−
 
 
|}
 
|}
  
Line 313: Line 431:
 
| not null
 
| not null
 
|  
 
|  
−
| Primary key for Product records.PK_Product_ProductID
+
| Primary key for Product records.<br />PK_Product_ProductID
 
|- valign="top"
 
|- valign="top"
 
| Name
 
| Name
Line 319: Line 437:
 
| not null
 
| not null
 
|  
 
|  
−
| Name of the product.AK_Product_Name
+
| Name of the product.<br />AK_Product_Name
 
|- valign="top"
 
|- valign="top"
 
| ProductNumber
 
| ProductNumber
Line 325: Line 443:
 
| not null
 
| not null
 
|  
 
|  
−
| Unique product identification number.AK_Product_ProductNumber
+
| Unique product identification number.<br />AK_Product_ProductNumber
 
|- valign="top"
 
|- valign="top"
 
| MakeFlag
 
| MakeFlag
Line 331: Line 449:
 
| not null
 
| not null
 
| (1)
 
| (1)
−
| 0 = Product is purchased, 1 = Product is manufactured in-house.
+
| 0 = Product is purchased, 1 = Product is manufactured in-house.<br />
 
|- valign="top"
 
|- valign="top"
 
| FinishedGoodsFlag
 
| FinishedGoodsFlag
Line 337: Line 455:
 
| not null
 
| not null
 
| (1)
 
| (1)
−
| 0 = Product is not a salable item. 1 = Product is salable.
+
| 0 = Product is not a salable item. 1 = Product is salable.<br />
 
|- valign="top"
 
|- valign="top"
 
| Color
 
| Color
Line 343: Line 461:
 
| null
 
| null
 
|  
 
|  
−
| Product color.
+
| Product color.<br />
 
|- valign="top"
 
|- valign="top"
 
| SafetyStockLevel
 
| SafetyStockLevel
Line 349: Line 467:
 
| not null
 
| not null
 
|  
 
|  
−
| Minimum inventory quantity.  
+
| Minimum inventory quantity. <br />
 
|- valign="top"
 
|- valign="top"
 
| ReorderPoint
 
| ReorderPoint
Line 355: Line 473:
 
| not null
 
| not null
 
|  
 
|  
−
| Inventory level that triggers a purchase order or work order.  
+
| Inventory level that triggers a purchase order or work order. <br />
 
|- valign="top"
 
|- valign="top"
 
| StandardCost
 
| StandardCost
Line 361: Line 479:
 
| not null
 
| not null
 
|  
 
|  
−
| Standard cost of the product.
+
| Standard cost of the product.<br />
 
|- valign="top"
 
|- valign="top"
 
| ListPrice
 
| ListPrice
Line 367: Line 485:
 
| not null
 
| not null
 
|  
 
|  
−
| Selling price.
+
| Selling price.<br />
 
|- valign="top"
 
|- valign="top"
 
| Size
 
| Size
Line 373: Line 491:
 
| null
 
| null
 
|  
 
|  
−
| Product size.
+
| Product size.<br />
 
|- valign="top"
 
|- valign="top"
 
| SizeUnitMeasureCode
 
| SizeUnitMeasureCode
Line 379: Line 497:
 
| null
 
| null
 
|  
 
|  
−
| Unit of measure for Size column.
+
| Unit of measure for Size column.<br />
 
|- valign="top"
 
|- valign="top"
 
| WeightUnitMeasureCode
 
| WeightUnitMeasureCode
Line 385: Line 503:
 
| null
 
| null
 
|  
 
|  
−
| Unit of measure for Weight column.
+
| Unit of measure for Weight column.<br />
 
|- valign="top"
 
|- valign="top"
 
| Weight
 
| Weight
Line 391: Line 509:
 
| null
 
| null
 
|  
 
|  
−
| Product weight.
+
| Product weight.<br />
 
|- valign="top"
 
|- valign="top"
 
| DaysToManufacture
 
| DaysToManufacture
Line 397: Line 515:
 
| not null
 
| not null
 
|  
 
|  
−
| Number of days required to manufacture the product.
+
| Number of days required to manufacture the product.<br />
 
|- valign="top"
 
|- valign="top"
 
| ProductLine
 
| ProductLine
Line 403: Line 521:
 
| null
 
| null
 
|  
 
|  
−
| R = Road, M = Mountain, T = Touring, S = Standard
+
| R = Road, M = Mountain, T = Touring, S = Standard<br />
 
|- valign="top"
 
|- valign="top"
 
| Class
 
| Class
Line 409: Line 527:
 
| null
 
| null
 
|  
 
|  
−
| H = High, M = Medium, L = Low
+
| H = High, M = Medium, L = Low<br />
 
|- valign="top"
 
|- valign="top"
 
| Style
 
| Style
Line 415: Line 533:
 
| null
 
| null
 
|  
 
|  
−
| W = Womens, M = Mens, U = Universal
+
| W = Womens, M = Mens, U = Universal<br />
 
|- valign="top"
 
|- valign="top"
 
| ProductSubcategoryID
 
| ProductSubcategoryID
Line 421: Line 539:
 
| null
 
| null
 
|  
 
|  
−
| Product is a member of this product subcategory. Foreign key to ProductSubCategory.ProductSubCategoryID.  
+
| Product is a member of this product subcategory. Foreign key to ProductSubCategory.ProductSubCategoryID. <br />
 
|- valign="top"
 
|- valign="top"
 
| ProductModelID
 
| ProductModelID
Line 427: Line 545:
 
| null
 
| null
 
|  
 
|  
−
| Product is a member of this product model. Foreign key to ProductModel.ProductModelID.
+
| Product is a member of this product model. Foreign key to ProductModel.ProductModelID.<br />
 
|- valign="top"
 
|- valign="top"
 
| SellStartDate
 
| SellStartDate
Line 433: Line 551:
 
| not null
 
| not null
 
|  
 
|  
−
| Date the product was available for sale.
+
| Date the product was available for sale.<br />
 
|- valign="top"
 
|- valign="top"
 
| SellEndDate
 
| SellEndDate
Line 439: Line 557:
 
| null
 
| null
 
|  
 
|  
−
| Date the product was no longer available for sale.
+
| Date the product was no longer available for sale.<br />
 
|- valign="top"
 
|- valign="top"
 
| DiscontinuedDate
 
| DiscontinuedDate
Line 445: Line 563:
 
| null
 
| null
 
|  
 
|  
−
| Date the product was discontinued.
+
| Date the product was discontinued.<br />
 
|- valign="top"
 
|- valign="top"
 
| rowguid
 
| rowguid
Line 451: Line 569:
 
| not null
 
| not null
 
| (NEWID())
 
| (NEWID())
−
| ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.AK_Product_rowguid
+
| ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.<br />AK_Product_rowguid
 
|- valign="top"
 
|- valign="top"
 
| ModifiedDate
 
| ModifiedDate
Line 457: Line 575:
 
| not null
 
| not null
 
| (GETDATE())
 
| (GETDATE())
−
| Date and time the record was last updated.
+
| Date and time the record was last updated.<br />
 
|}
 
|}
  
Line 554: Line 672:
 
{| 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"
 +
| '''Dependency Type
 +
| '''Object Type
 
| '''Referenced Object
 
| '''Referenced Object
−
| '''Object Type
+
 
−
| '''Dependency Type
 
−
 
 
|-
 
|-
 +
| Data Type
 +
| Type
 
| [[dbo.Flag_(type)|dbo.Flag]]
 
| [[dbo.Flag_(type)|dbo.Flag]]
 +
 +
|-
 +
| Data Type
 
| Type
 
| Type
−
| Data Type
+
| [[dbo.Name_(type)|dbo.Name]]
 +
 
 
|-
 
|-
−
| [[dbo.Name_(type)|dbo.Name]]
+
| Schema
−
| Type
+
| Schema
−
| Data Type
+
| [[Production_(schema)|Production]]
 +
 
 
|}
 
|}
  
Line 571: Line 696:
 
{| 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"
 +
| '''Reference Type
 +
| '''Object Type
 
| '''Referencing Object
 
| '''Referencing Object
−
| '''Object Type
+
 
−
| '''Reference Type
 
−
 
 
|-
 
|-
 +
| Select
 +
| View
 
| [[Production.vProductAndDescription_(view)|Production.vProductAndDescription]]
 
| [[Production.vProductAndDescription_(view)|Production.vProductAndDescription]]
−
| View
+
 
 +
|-
 
| Select
 
| Select
 +
| Procedure
 +
| [[dbo.uspGetBillOfMaterials_(procedure)|dbo.uspGetBillOfMaterials]]
 +
 
|-
 
|-
−
| [[dbo.uspGetBillOfMaterials_(procedure)|dbo.uspGetBillOfMaterials]]
+
| Select
 
| Procedure
 
| Procedure
 +
| [[dbo.uspGetWhereUsedProductID_(procedure)|dbo.uspGetWhereUsedProductID]]
 +
 +
|-
 
| Select
 
| Select
 +
| Function
 +
| [[dbo.ufnGetProductDealerPrice_(function)|dbo.ufnGetProductDealerPrice]]
 +
 
|-
 
|-
−
| [[dbo.uspGetWhereUsedProductID_(procedure)|dbo.uspGetWhereUsedProductID]]
 
−
| Procedure
 
 
| Select
 
| Select
 +
| Function
 +
| [[dbo.ufnGetProductListPrice_(function)|dbo.ufnGetProductListPrice]]
 +
 
|-
 
|-
−
| [[dbo.ufnGetProductDealerPrice_(function)|dbo.ufnGetProductDealerPrice]]
 
−
| Function
 
 
| Select
 
| Select
−
|-
 
−
| [[dbo.ufnGetProductListPrice_(function)|dbo.ufnGetProductListPrice]]
 
 
| Function
 
| Function
−
| Select
 
−
|-
 
 
| [[dbo.ufnGetProductStandardCost_(function)|dbo.ufnGetProductStandardCost]]
 
| [[dbo.ufnGetProductStandardCost_(function)|dbo.ufnGetProductStandardCost]]
−
| Function
+
 
−
| Select
 
 
|}
 
|}

Latest revision as of 10:55, 27 August 2011

wikibot[edit]

Table Production.Product
Description Products sold or used in the manfacturing of sold products.

Columns[edit]

Column Data Type Nullable Default Description
ProductID int not null Primary key for Product records.
Name dbo.Name not null Name of the product.
ProductNumber nvarchar(25) not null Unique product identification number.
MakeFlag dbo.Flag not null ((1)) 0 = Product is purchased, 1 = Product is manufactured in-house.
FinishedGoodsFlag dbo.Flag not null ((1)) 0 = Product is not a salable item. 1 = Product is salable.
Color nvarchar(15) null Product color.
SafetyStockLevel smallint not null Minimum inventory quantity.
ReorderPoint smallint not null Inventory level that triggers a purchase order or work order.
StandardCost money not null Standard cost of the product.
ListPrice money not null Selling price.
Size nvarchar(5) null Product size.
SizeUnitMeasureCode nchar(3) null Unit of measure for Size column.
WeightUnitMeasureCode nchar(3) null Unit of measure for Weight column.
Weight decimal(8, 2) null Product weight.
DaysToManufacture int not null Number of days required to manufacture the product.
ProductLine nchar(2) null R = Road, M = Mountain, T = Touring, S = Standard
Class nchar(2) null H = High, M = Medium, L = Low
Style nchar(2) null W = Womens, M = Mens, U = Universal
ProductSubcategoryID int null Product is a member of this product subcategory. Foreign key to ProductSubCategory.ProductSubCategoryID.
ProductModelID int null Product is a member of this product model. Foreign key to ProductModel.ProductModelID.
SellStartDate datetime not null Date the product was available for sale.
SellEndDate datetime null Date the product was no longer available for sale.
DiscontinuedDate datetime null Date the product was discontinued.
rowguid uniqueidentifier not null (newid()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate datetime not null (getdate()) Date and time the record was last updated.

Primary Key[edit]

Primary Key Columns
PK_Product_ProductID ProductID

Indexes[edit]

Index Type Columns
AK_Product_Name Unique Name
AK_Product_ProductNumber Unique ProductNumber
AK_Product_rowguid Unique rowguid

Check Constraints[edit]

Check Constraint Expression Description
CK_Product_Class (upper([Class])='H' OR upper([Class])='M' OR upper([Class])='L' OR [Class] IS NULL) Check constraint [Class]='h' OR [Class]='m' OR [Class]='l' OR [Class]='H' OR [Class]='M' OR [Class]='L' OR [Class] IS NULL
CK_Product_DaysToManufacture ([DaysToManufacture]>=(0)) Check constraint [DaysToManufacture] >= (0)
CK_Product_ListPrice ([ListPrice]>=(0.00)) Check constraint [ListPrice] >= (0.00)
CK_Product_ProductLine (upper([ProductLine])='R' OR upper([ProductLine])='M' OR upper([ProductLine])='T' OR upper([ProductLine])='S' OR [ProductLine] IS NULL) Check constraint [ProductLine]='r' OR [ProductLine]='m' OR [ProductLine]='t' OR [ProductLine]='s' OR [ProductLine]='R' OR [ProductLine]='M' OR [ProductLine]='T' OR [ProductLine]='S' OR [ProductLine] IS NULL
CK_Product_ReorderPoint ([ReorderPoint]>(0)) Check constraint [ReorderPoint] > (0)
CK_Product_SafetyStockLevel ([SafetyStockLevel]>(0)) Check constraint [SafetyStockLevel] > (0)
CK_Product_SellEndDate ([SellEndDate]>=[SellStartDate] OR [SellEndDate] IS NULL) Check constraint [SellEndDate] >= [SellStartDate] OR [SellEndDate] IS NULL
CK_Product_StandardCost ([StandardCost]>=(0.00)) Check constraint [SafetyStockLevel] > (0)
CK_Product_Style (upper([Style])='U' OR upper([Style])='M' OR upper([Style])='W' OR [Style] IS NULL) Check constraint [Style]='u' OR [Style]='m' OR [Style]='w' OR [Style]='U' OR [Style]='M' OR [Style]='W' OR [Style] IS NULL
CK_Product_Weight ([Weight]>(0.00)) Check constraint [Weight] > (0.00)

Foreign Keys[edit]

Relation Column Referenced Column
Production.ProductModel ProductModelID ProductModelID
Production.ProductSubcategory ProductSubcategoryID ProductSubcategoryID
Production.UnitMeasure SizeUnitMeasureCode UnitMeasureCode
Production.UnitMeasure WeightUnitMeasureCode UnitMeasureCode

Detail Tables[edit]

Detail Table Column Referencing Column
Production.BillOfMaterials ProductID ComponentID
Production.BillOfMaterials ProductID ProductAssemblyID
Production.ProductCostHistory ProductID ProductID
Production.ProductDocument ProductID ProductID
Production.ProductInventory ProductID ProductID
Production.ProductListPriceHistory ProductID ProductID
Production.ProductProductPhoto ProductID ProductID
Production.ProductReview ProductID ProductID
Purchasing.ProductVendor ProductID ProductID
Purchasing.PurchaseOrderDetail ProductID ProductID
Sales.ShoppingCartItem ProductID ProductID
Sales.SpecialOfferProduct ProductID ProductID
Production.TransactionHistory ProductID ProductID
Production.WorkOrder ProductID ProductID

References[edit]

Dependency Type Object Type Referenced Object
Data Type Type dbo.Flag
Data Type Type dbo.Name
Schema Schema Production

Dependencies[edit]

Reference Type Object Type Referencing Object
Select View Production.vProductAndDescription
Select Procedure dbo.uspGetBillOfMaterials
Select Procedure dbo.uspGetWhereUsedProductID
Select Function dbo.ufnGetProductDealerPrice
Select Function dbo.ufnGetProductListPrice
Select Function dbo.ufnGetProductStandardCost


automatically generated[edit]

Table Production.Product
Description Products sold or used in the manfacturing of sold products.


Column Data Type Nullable Default Description / PK / Index
ProductID int not null Primary key for Product records.
PK_Product_ProductID
Name Name not null Name of the product.
AK_Product_Name
ProductNumber nvarchar(25) not null Unique product identification number.
AK_Product_ProductNumber
MakeFlag Flag not null (1) 0 = Product is purchased, 1 = Product is manufactured in-house.
FinishedGoodsFlag Flag not null (1) 0 = Product is not a salable item. 1 = Product is salable.
Color nvarchar(15) null Product color.
SafetyStockLevel smallint not null Minimum inventory quantity.
ReorderPoint smallint not null Inventory level that triggers a purchase order or work order.
StandardCost money not null Standard cost of the product.
ListPrice money not null Selling price.
Size nvarchar(5) null Product size.
SizeUnitMeasureCode nchar(3) null Unit of measure for Size column.
WeightUnitMeasureCode nchar(3) null Unit of measure for Weight column.
Weight decimal(8, 2) null Product weight.
DaysToManufacture int not null Number of days required to manufacture the product.
ProductLine nchar(2) null R = Road, M = Mountain, T = Touring, S = Standard
Class nchar(2) null H = High, M = Medium, L = Low
Style nchar(2) null W = Womens, M = Mens, U = Universal
ProductSubcategoryID int null Product is a member of this product subcategory. Foreign key to ProductSubCategory.ProductSubCategoryID.
ProductModelID int null Product is a member of this product model. Foreign key to ProductModel.ProductModelID.
SellStartDate datetime not null Date the product was available for sale.
SellEndDate datetime null Date the product was no longer available for sale.
DiscontinuedDate datetime null Date the product was discontinued.
rowguid uniqueidentifier not null (NEWID()) ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
AK_Product_rowguid
ModifiedDate datetime not null (GETDATE()) Date and time the record was last updated.


Relation Column Referenced Column
Production.ProductModel ProductModelID ProductModelID
Production.ProductSubcategory ProductSubcategoryID ProductSubcategoryID
Production.UnitMeasure SizeUnitMeasureCode UnitMeasureCode
Production.UnitMeasure WeightUnitMeasureCode UnitMeasureCode


Detail Table Column Referencing Column
Production.BillOfMaterials ProductID ComponentID
Production.BillOfMaterials ProductID ProductAssemblyID
Production.ProductCostHistory ProductID ProductID
Production.ProductDocument ProductID ProductID
Production.ProductInventory ProductID ProductID
Production.ProductListPriceHistory ProductID ProductID
Production.ProductProductPhoto ProductID ProductID
Production.ProductReview ProductID ProductID
Purchasing.ProductVendor ProductID ProductID
Purchasing.PurchaseOrderDetail ProductID ProductID
Sales.ShoppingCartItem ProductID ProductID
Sales.SpecialOfferProduct ProductID ProductID
Production.TransactionHistory ProductID ProductID
Production.WorkOrder ProductID ProductID


Dependency Type Object Type Referenced Object
Data Type Type dbo.Flag
Data Type Type dbo.Name
Schema Schema Production


Reference Type Object Type Referencing Object
Select View Production.vProductAndDescription
Select Procedure dbo.uspGetBillOfMaterials
Select Procedure dbo.uspGetWhereUsedProductID
Select Function dbo.ufnGetProductDealerPrice
Select Function dbo.ufnGetProductListPrice
Select Function dbo.ufnGetProductStandardCost
Retrieved from "http://dbshelp.devio.at/index.php?title=Production.Product_(table)&oldid=2934"