Production.Product (table)

wikibot

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

Columns

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

Primary Key	Columns
PK_Product_ProductID	ProductID

Indexes

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

Check Constraints

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

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

Detail Tables

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

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

Dependencies

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

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