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