Production.Product Table

Description

Products sold or used in the manfacturing of sold products.

Properties

Creation Date	27/10/2017 14:33
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	504
Data Space Used	104.00 KB
Index Space Used	128.00 KB

Columns

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

Indexes

Index	Description	Primary	Unique
AK_Product_Name	Unique nonclustered index.
AK_Product_ProductNumber	Unique nonclustered index.
AK_Product_rowguid	Unique nonclustered index. Used to support replication samples.
PK_Product_ProductID	Primary key (clustered) constraint

Check Constraints

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

Relationships

Relationship	Description
FK_BillOfMaterials_Product_ComponentID	Foreign key constraint referencing Product.ComponentID.
FK_BillOfMaterials_Product_ProductAssemblyID	Foreign key constraint referencing Product.ProductAssemblyID.
FK_Product_ProductModel_ProductModelID	Foreign key constraint referencing ProductModel.ProductModelID.
FK_Product_ProductSubcategory_ProductSubcategoryID	Foreign key constraint referencing ProductSubcategory.ProductSubcategoryID.
FK_Product_UnitMeasure_SizeUnitMeasureCode	Foreign key constraint referencing UnitMeasure.UnitMeasureCode.
FK_Product_UnitMeasure_WeightUnitMeasureCode	Foreign key constraint referencing UnitMeasure.UnitMeasureCode.
FK_ProductCostHistory_Product_ProductID	Foreign key constraint referencing Product.ProductID.
FK_ProductDocument_Product_ProductID	Foreign key constraint referencing Product.ProductID.
FK_ProductInventory_Product_ProductID	Foreign key constraint referencing Product.ProductID.
FK_ProductListPriceHistory_Product_ProductID	Foreign key constraint referencing Product.ProductID.
FK_ProductProductPhoto_Product_ProductID	Foreign key constraint referencing Product.ProductID.
FK_ProductReview_Product_ProductID	Foreign key constraint referencing Product.ProductID.
FK_ProductVendor_Product_ProductID	Foreign key constraint referencing Product.ProductID.
FK_PurchaseOrderDetail_Product_ProductID	Foreign key constraint referencing Product.ProductID.
FK_ShoppingCartItem_Product_ProductID	Foreign key constraint referencing Product.ProductID.
FK_SpecialOfferProduct_Product_ProductID	Foreign key constraint referencing Product.ProductID.
FK_TransactionHistory_Product_ProductID	Foreign key constraint referencing Product.ProductID.
FK_WorkOrder_Product_ProductID	Foreign key constraint referencing Product.ProductID.

Objects that depend on Production.Product

Database Object	Object Type	Description	Dep Level
Production.BillOfMaterials	Table	Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.	1
iduSalesOrderDetail	Trigger	AFTER INSERT, DELETE, UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in SalesOrderDetail and updates the SalesOrderHeader.SubTotal column.	3
iPurchaseOrderDetail	Trigger	AFTER INSERT trigger that inserts a row in the TransactionHistory table and updates the PurchaseOrderHeader.SubTotal column.	2
iWorkOrder	Trigger	AFTER INSERT trigger that inserts a row in the TransactionHistory table.	2
Production.ProductCostHistory	Table	Changes in the cost of a product over time.	1
Production.ProductDocument	Table	Cross-reference table mapping products to related product documents.	1
Production.ProductInventory	Table	Product inventory information.	1
Production.ProductListPriceHistory	Table	Changes in the list price of a product over time.	1
Production.ProductProductPhoto	Table	Cross-reference table mapping products and product photos.	1
Production.ProductReview	Table	Customer reviews of products they have purchased.	1
Purchasing.ProductVendor	Table	Vendors are added to the table before any POs are processed.	1
Purchasing.PurchaseOrderDetail	Table	Individual products associated with a specific purchase order. See PurchaseOrderHeader.	1
Sales.SalesOrderDetail	Table	Individual products associated with a specific sales order. See SalesOrderHeader.	2
Sales.ShoppingCartItem	Table	Contains online customer orders until the order is submitted or cancelled.	1
Sales.SpecialOfferProduct	Table	Cross-reference table mapping products to special offer discounts.	1
Production.TransactionHistory	Table	Record of each purchase order, sales order, or work order transaction year to date.	1
dbo.ufnGetProductDealerPrice	User Defined Function	Scalar function returning the dealer price for a given product on a particular order date.	1
dbo.ufnGetProductListPrice	User Defined Function	Scalar function returning the list price for a given product on a particular order date.	1
dbo.ufnGetProductStandardCost	User Defined Function	Scalar function returning the standard cost for a given product on a particular order date.	1
dbo.ufnGetStock	User Defined Function	Scalar function returning the quantity of inventory in LocationID 6 (Miscellaneous Storage)for a specified ProductID.	2
uPurchaseOrderDetail	Trigger	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in PurchaseOrderDetail and updates the PurchaseOrderHeader.SubTotal column.	2
dbo.uspGetBillOfMaterials	Stored Procedure	Stored procedure using a recursive query to return a multi-level bill of material for the specified ProductID.	1
dbo.uspGetWhereUsedProductID	Stored Procedure	Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID.	1
uWorkOrder	Trigger	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in the WorkOrder table.	2
Production.vProductAndDescription	View	Product names and descriptions. Product descriptions are provided in multiple languages.	1
Production.WorkOrder	Table	Manufacturing work orders.	1
Production.WorkOrderRouting	Table	Work order details.	2

Objects that Production.Product depends on

Database Object	Object Type	Description	Dep Level
dbo.Flag	User Defined Data Type		1
dbo.Name	User Defined Data Type		1
Production.ProductCategory	Table	High-level product categorization.	2
Production.ProductModel	Table	Product model classification.	1
Production.ProductSubcategory	Table	Product subcategories. See ProductCategory table.	1
Production.UnitMeasure	Table	Unit of measure lookup table.	1

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[Product](
    [ProductID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [ProductNumber] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [MakeFlag] [dbo].[Flag] NOT NULL,
    [FinishedGoodsFlag] [dbo].[Flag] NOT NULL,
    [Color] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [SafetyStockLevel] [smallint] NOT NULL,
    [ReorderPoint] [smallint] NOT NULL,
    [StandardCost] [money] NOT NULL,
    [ListPrice] [money] NOT NULL,
    [Size] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [SizeUnitMeasureCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [WeightUnitMeasureCode] [nchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Weight] [decimal](8, 2) NULL,
    [DaysToManufacture] [int] NOT NULL,
    [ProductLine] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Class] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Style] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ProductSubcategoryID] [int] NULL,
    [ProductModelID] [int] NULL,
    [SellStartDate] [datetime] NOT NULL,
    [SellEndDate] [datetime] NULL,
    [DiscontinuedDate] [datetime] NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Product_ProductID] PRIMARY KEY CLUSTERED 
(
    [ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [Production].[Product] ADD  CONSTRAINT [DF_Product_MakeFlag]  DEFAULT ((1)) FOR [MakeFlag]
ALTER TABLE [Production].[Product] ADD  CONSTRAINT [DF_Product_FinishedGoodsFlag]  DEFAULT ((1)) FOR [FinishedGoodsFlag]
ALTER TABLE [Production].[Product] ADD  CONSTRAINT [DF_Product_rowguid]  DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Production].[Product] ADD  CONSTRAINT [DF_Product_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_ProductModel_ProductModelID] FOREIGN KEY([ProductModelID])
REFERENCES [Production].[ProductModel] ([ProductModelID])
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [FK_Product_ProductModel_ProductModelID]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_ProductSubcategory_ProductSubcategoryID] FOREIGN KEY([ProductSubcategoryID])
REFERENCES [Production].[ProductSubcategory] ([ProductSubcategoryID])
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [FK_Product_ProductSubcategory_ProductSubcategoryID]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_UnitMeasure_SizeUnitMeasureCode] FOREIGN KEY([SizeUnitMeasureCode])
REFERENCES [Production].[UnitMeasure] ([UnitMeasureCode])
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [FK_Product_UnitMeasure_SizeUnitMeasureCode]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [FK_Product_UnitMeasure_WeightUnitMeasureCode] FOREIGN KEY([WeightUnitMeasureCode])
REFERENCES [Production].[UnitMeasure] ([UnitMeasureCode])
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [FK_Product_UnitMeasure_WeightUnitMeasureCode]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [CK_Product_Class] CHECK  ((upper([Class])='H' OR upper([Class])='M' OR upper([Class])='L' OR [Class] IS NULL))
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [CK_Product_Class]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [CK_Product_DaysToManufacture] CHECK  (([DaysToManufacture]>=(0)))
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [CK_Product_DaysToManufacture]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [CK_Product_ListPrice] CHECK  (([ListPrice]>=(0.00)))
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [CK_Product_ListPrice]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [CK_Product_ProductLine] CHECK  ((upper([ProductLine])='R' OR upper([ProductLine])='M' OR upper([ProductLine])='T' OR upper([ProductLine])='S' OR [ProductLine] IS NULL))
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [CK_Product_ProductLine]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [CK_Product_ReorderPoint] CHECK  (([ReorderPoint]>(0)))
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [CK_Product_ReorderPoint]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [CK_Product_SafetyStockLevel] CHECK  (([SafetyStockLevel]>(0)))
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [CK_Product_SafetyStockLevel]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [CK_Product_SellEndDate] CHECK  (([SellEndDate]>=[SellStartDate] OR [SellEndDate] IS NULL))
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [CK_Product_SellEndDate]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [CK_Product_StandardCost] CHECK  (([StandardCost]>=(0.00)))
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [CK_Product_StandardCost]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [CK_Product_Style] CHECK  ((upper([Style])='U' OR upper([Style])='M' OR upper([Style])='W' OR [Style] IS NULL))
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [CK_Product_Style]
ALTER TABLE [Production].[Product]  WITH CHECK ADD  CONSTRAINT [CK_Product_Weight] CHECK  (([Weight]>(0.00)))
ALTER TABLE [Production].[Product] CHECK CONSTRAINT [CK_Product_Weight]

Related Objects