Production.Product

Object type: Table

Products sold or used in the manfacturing of sold products.

Columns

Name Data type Nullable Description

ProductID int No Column has identity seed value of 1 with a step value of 1.
Primary key for Product records.

Name Name No Name of the product.

ProductNumber nvarchar(25) No Unique product identification number.

MakeFlag Flag No Default value: 1
0 = Product is purchased, 1 = Product is manufactured in-house.

FinishedGoodsFlag Flag No Default value: 1
0 = Product is not a salable item. 1 = Product is salable.

Color nvarchar(15) Yes Product color.

SafetyStockLevel smallint No Minimum inventory quantity.

ReorderPoint smallint No Inventory level that triggers a purchase order or work order.

StandardCost money No Standard cost of the product.

ListPrice money No Selling price.

Size nvarchar(5) Yes Product size.

SizeUnitMeasureCode nchar(3) Yes Unit of measure for Size column.

WeightUnitMeasureCode nchar(3) Yes Unit of measure for Weight column.

Weight decimal(8, 2) Yes Product weight.

DaysToManufacture int No Number of days required to manufacture the product.

ProductLine nchar(2) Yes R = Road, M = Mountain, T = Touring, S = Standard

Class nchar(2) Yes H = High, M = Medium, L = Low

Style nchar(2) Yes W = Womens, M = Mens, U = Universal

ProductSubcategoryID int Yes Product is a member of this product subcategory. Foreign key to ProductSubCategory.ProductSubCategoryID.

ProductModelID int Yes Product is a member of this product model. Foreign key to ProductModel.ProductModelID.

SellStartDate datetime No Date the product was available for sale.

SellEndDate datetime Yes Date the product was no longer available for sale.

DiscontinuedDate datetime Yes Date the product was discontinued.

rowguid uniqueidentifier No Default value: NEWID()
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.

ModifiedDate datetime No Default value: GETDATE()
Date and time the record was last updated.

Constraints

Name Type Columns Description

DF_Product_MakeFlag DEFAULT MakeFlag Value: 1
Default constraint value of 1

DF_Product_FinishedGoodsFlag DEFAULT FinishedGoodsFlag Value: 1
Default constraint value of 1

DF_Product_rowguid DEFAULT rowguid Value: NEWID()
Default constraint value of NEWID()

DF_Product_ModifiedDate DEFAULT ModifiedDate Value: GETDATE()
Default constraint value of GETDATE()

CK_Product_SafetyStockLevel CHECK N/A Value: ([SafetyStockLevel] > 0)
Check constraint [SafetyStockLevel]>(0)

CK_Product_ReorderPoint CHECK N/A Value: ([ReorderPoint] > 0)
Check constraint [ReorderPoint]>(0)

CK_Product_StandardCost CHECK N/A Value: ([StandardCost] >= 0.00)
Check constraint [SafetyStockLevel]>(0)

CK_Product_ListPrice CHECK N/A Value: ([ListPrice] >= 0.00)
Check constraint [ListPrice]>=(0.00)

CK_Product_Weight CHECK N/A Value: ([Weight] > 0.00)
Check constraint [Weight]>(0.00)

CK_Product_DaysToManufacture CHECK N/A Value: ([DaysToManufacture] >= 0)
Check constraint [DaysToManufacture]>=(0)

CK_Product_ProductLine CHECK N/A Value: (UPPER([ProductLine]) IN ('S', 'T', 'M', 'R') 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_Class CHECK N/A Value: (UPPER([Class]) IN ('L', 'M', 'H') 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_Style CHECK N/A Value: (UPPER([Style]) IN ('W', 'M', 'U') 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_SellEndDate CHECK N/A Value: (([SellEndDate] >= [SellStartDate]) OR ([SellEndDate] IS NULL) )
Check constraint [SellEndDate] >= [SellStartDate] OR [SellEndDate] IS NULL

PK_Product_ProductID PRIMARY KEY ProductID Clustered index created by a primary key constraint.

FK_Product_UnitMeasure_SizeUnitMeasureCode FOREIGN KEY SizeUnitMeasureCode Reference table: Production.UnitMeasure
Foreign key constraint referencing UnitMeasure.UnitMeasureCode.

FK_Product_UnitMeasure_WeightUnitMeasureCode FOREIGN KEY WeightUnitMeasureCode Reference table: Production.UnitMeasure
Foreign key constraint referencing UnitMeasure.UnitMeasureCode.

FK_Product_ProductModel_ProductModelID FOREIGN KEY ProductModelID Reference table: Production.ProductModel
Foreign key constraint referencing ProductModel.ProductModelID.

FK_Product_ProductSubcategory_ProductSubcategoryID FOREIGN KEY ProductSubcategoryID Reference table: Production.ProductSubcategory
Foreign key constraint referencing ProductSubcategory.ProductSubcategoryID.

Indices

Name Description

AK_Product_Name Unique nonclustered index.

AK_Product_ProductNumber Unique nonclustered index.

AK_Product_rowguid Unique nonclustered index.Used to support replication samples.

Triggers

Name Description

Production.uProduct AFTER UPDATE trigger setting the ModifiedDate column in the Product table to the current date.

Linked tables

Name Description

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

Production.ProductCostHistory Changes in the cost of a product over time.

Production.ProductDocument Cross-reference table mapping products to related product documents.

Production.ProductInventory Product inventory information.

Production.ProductListPriceHistory Changes in the list price of a product over time.

Production.ProductProductPhoto Cross-reference table mapping products and product photos.

Production.ProductReview Customer reviews of products they have purchased.

Production.TransactionHistory Record of each purchase order, sales order, or work order transaction year to date.

Production.WorkOrder Manufacturing work orders.

Purchasing.ProductVendor Cross-reference table mapping vendors with the products they supply.

Purchasing.PurchaseOrderDetail Individual products associated with a specific purchase order. See PurchaseOrderHeader.

Sales.ShoppingCartItem Contains online customer orders until the order is submitted or cancelled.

Sales.SpecialOfferProduct Cross-reference table mapping products to special offer discounts.

Accessed by

Name Description

Company Sales Adventure Works sales by quarter and product category. This report illustrates the use of a matrix data region that provides drilldown from summary data into detail data by showing and hiding rows. This report also illustrates the use of background images.

dbo.ufnGetProductDealerPrice Scalar function returning the dealer price for a given product on a particular order date.

dbo.ufnGetProductListPrice Scalar function returning the list price for a given product on a particular order date.

dbo.ufnGetProductStandardCost Scalar function returning the standard cost for a given product on a particular order date.

dbo.uspGetBillOfMaterials Stored procedure using a recursive query to return a multi-level bill of material for the specified ProductID.

dbo.uspGetWhereUsedProductID Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID.

Employee Sales Summary Adventure Works sales for an individual employee. This report includes Sales Comparison and Current Month Sales Comparison charts in addition to a Current Month Order Summary table with drillthrough to individual orders. This report illustrates the use of multiple datasets, charts, tables, drillthrough, and dynamic parameters.

Product Catalog Adventure Works full product catalog with pictures. This report illustrates the use of embedded images, database images, page breaks, page footers, tables, conditional formatting, and a document map.

Product Line Sales Adventure Works top five sales people and stores. This report illustrates a dataset with queries containing the TOP clause. It also illustrates the use of tables, charts, parameters, calculated fields, and drillthrough links.

Production.vProductAndDescription Product names and descriptions. Product descriptions are provided in multiple languages.

Sales Order Detail Detail of an individual Adventure Works order. This report can be accessed as a drillthrough report from the Employee Sales Summary and Territory Sales drilldown report. This report illustrates the use of lists, tables, parameters, and expressions.

Modified by

Name Description

Production.uProduct AFTER UPDATE trigger setting the ModifiedDate column in the Product table to the current date.

Applies to

Tables | User

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

Name	Type	Columns	Description
DF_Product_MakeFlag	DEFAULT	MakeFlag	Value: 1 Default constraint value of 1
DF_Product_FinishedGoodsFlag	DEFAULT	FinishedGoodsFlag	Value: 1 Default constraint value of 1
DF_Product_rowguid	DEFAULT	rowguid	Value: NEWID() Default constraint value of NEWID()
DF_Product_ModifiedDate	DEFAULT	ModifiedDate	Value: GETDATE() Default constraint value of GETDATE()
CK_Product_SafetyStockLevel	CHECK	N/A	Value: ([SafetyStockLevel] > 0) Check constraint [SafetyStockLevel]>(0)
CK_Product_ReorderPoint	CHECK	N/A	Value: ([ReorderPoint] > 0) Check constraint [ReorderPoint]>(0)
CK_Product_StandardCost	CHECK	N/A	Value: ([StandardCost] >= 0.00) Check constraint [SafetyStockLevel]>(0)
CK_Product_ListPrice	CHECK	N/A	Value: ([ListPrice] >= 0.00) Check constraint [ListPrice]>=(0.00)
CK_Product_Weight	CHECK	N/A	Value: ([Weight] > 0.00) Check constraint [Weight]>(0.00)
CK_Product_DaysToManufacture	CHECK	N/A	Value: ([DaysToManufacture] >= 0) Check constraint [DaysToManufacture]>=(0)
CK_Product_ProductLine	CHECK	N/A	Value: (UPPER([ProductLine]) IN ('S', 'T', 'M', 'R') 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_Class	CHECK	N/A	Value: (UPPER([Class]) IN ('L', 'M', 'H') 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_Style	CHECK	N/A	Value: (UPPER([Style]) IN ('W', 'M', 'U') 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_SellEndDate	CHECK	N/A	Value: (([SellEndDate] >= [SellStartDate]) OR ([SellEndDate] IS NULL) ) Check constraint [SellEndDate] >= [SellStartDate] OR [SellEndDate] IS NULL
PK_Product_ProductID	PRIMARY KEY	ProductID	Clustered index created by a primary key constraint.
FK_Product_UnitMeasure_SizeUnitMeasureCode	FOREIGN KEY	SizeUnitMeasureCode	Reference table: Production.UnitMeasure Foreign key constraint referencing UnitMeasure.UnitMeasureCode.
FK_Product_UnitMeasure_WeightUnitMeasureCode	FOREIGN KEY	WeightUnitMeasureCode	Reference table: Production.UnitMeasure Foreign key constraint referencing UnitMeasure.UnitMeasureCode.
FK_Product_ProductModel_ProductModelID	FOREIGN KEY	ProductModelID	Reference table: Production.ProductModel Foreign key constraint referencing ProductModel.ProductModelID.
FK_Product_ProductSubcategory_ProductSubcategoryID	FOREIGN KEY	ProductSubcategoryID	Reference table: Production.ProductSubcategory Foreign key constraint referencing ProductSubcategory.ProductSubcategoryID.

Name	Description
AK_Product_Name	Unique nonclustered index.
AK_Product_ProductNumber	Unique nonclustered index.
AK_Product_rowguid	Unique nonclustered index.Used to support replication samples.

Name	Description
Production.uProduct	AFTER UPDATE trigger setting the ModifiedDate column in the Product table to the current date.

Name	Description
Production.BillOfMaterials	Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.
Production.ProductCostHistory	Changes in the cost of a product over time.
Production.ProductDocument	Cross-reference table mapping products to related product documents.
Production.ProductInventory	Product inventory information.
Production.ProductListPriceHistory	Changes in the list price of a product over time.
Production.ProductProductPhoto	Cross-reference table mapping products and product photos.
Production.ProductReview	Customer reviews of products they have purchased.
Production.TransactionHistory	Record of each purchase order, sales order, or work order transaction year to date.
Production.WorkOrder	Manufacturing work orders.
Purchasing.ProductVendor	Cross-reference table mapping vendors with the products they supply.
Purchasing.PurchaseOrderDetail	Individual products associated with a specific purchase order. See PurchaseOrderHeader.
Sales.ShoppingCartItem	Contains online customer orders until the order is submitted or cancelled.
Sales.SpecialOfferProduct	Cross-reference table mapping products to special offer discounts.

Name	Description
Company Sales	Adventure Works sales by quarter and product category. This report illustrates the use of a matrix data region that provides drilldown from summary data into detail data by showing and hiding rows. This report also illustrates the use of background images.
dbo.ufnGetProductDealerPrice	Scalar function returning the dealer price for a given product on a particular order date.
dbo.ufnGetProductListPrice	Scalar function returning the list price for a given product on a particular order date.
dbo.ufnGetProductStandardCost	Scalar function returning the standard cost for a given product on a particular order date.
dbo.uspGetBillOfMaterials	Stored procedure using a recursive query to return a multi-level bill of material for the specified ProductID.
dbo.uspGetWhereUsedProductID	Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID.
Employee Sales Summary	Adventure Works sales for an individual employee. This report includes Sales Comparison and Current Month Sales Comparison charts in addition to a Current Month Order Summary table with drillthrough to individual orders. This report illustrates the use of multiple datasets, charts, tables, drillthrough, and dynamic parameters.
Product Catalog	Adventure Works full product catalog with pictures. This report illustrates the use of embedded images, database images, page breaks, page footers, tables, conditional formatting, and a document map.
Product Line Sales	Adventure Works top five sales people and stores. This report illustrates a dataset with queries containing the TOP clause. It also illustrates the use of tables, charts, parameters, calculated fields, and drillthrough links.
Production.vProductAndDescription	Product names and descriptions. Product descriptions are provided in multiple languages.
Sales Order Detail	Detail of an individual Adventure Works order. This report can be accessed as a drillthrough report from the Employee Sales Summary and Territory Sales drilldown report. This report illustrates the use of lists, tables, parameters, and expressions.