Properties

Owner:	Production
Creation Date:	04/26/2006
Located On:	PRIMARY
Data Size KB:	104
Index Size KB:	120
Rows:	504
Description:	Products sold or used in the manfacturing of sold products.

Columns

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

Total: 25 column(s)

Identity column

	Name	Seed	Increment	Not for replication
	ProductID	1	1

Indexes

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

Total: 4 index(es)

Check Constraints

	Name	Expression
	CK_Product_Class	(upper([Class])='H' OR upper([Class])='M' OR upper([Class])='L' OR [Class] IS NULL)
	CK_Product_DaysToManufacture	([DaysToManufacture]>=(0))
	CK_Product_ListPrice	([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)
	CK_Product_ReorderPoint	([ReorderPoint]>(0))
	CK_Product_SafetyStockLevel	([SafetyStockLevel]>(0))
	CK_Product_SellEndDate	([SellEndDate]>=[SellStartDate] OR [SellEndDate] IS NULL)
	CK_Product_StandardCost	([StandardCost]>=(0.00))
	CK_Product_Style	(upper([Style])='U' OR upper([Style])='M' OR upper([Style])='W' OR [Style] IS NULL)
	CK_Product_Weight	([Weight]>(0.00))

Total: 10 constraint(s)

Referencing Tables

	Table	Foreign Key	Primary Key or Unique Constraint
	Production.ProductCostHistory	FK_ProductCostHistory_Product_ProductID	PK_Product_ProductID
	Production.ProductDocument	FK_ProductDocument_Product_ProductID	PK_Product_ProductID
	Production.ProductInventory	FK_ProductInventory_Product_ProductID	PK_Product_ProductID
	Production.ProductListPriceHistory	FK_ProductListPriceHistory_Product_ProductID	PK_Product_ProductID
	Production.ProductProductPhoto	FK_ProductProductPhoto_Product_ProductID	PK_Product_ProductID
	Production.ProductReview	FK_ProductReview_Product_ProductID	PK_Product_ProductID
	Purchasing.ProductVendor	FK_ProductVendor_Product_ProductID	PK_Product_ProductID
	Purchasing.PurchaseOrderDetail	FK_PurchaseOrderDetail_Product_ProductID	PK_Product_ProductID
	Sales.ShoppingCartItem	FK_ShoppingCartItem_Product_ProductID	PK_Product_ProductID
	Sales.SpecialOfferProduct	FK_SpecialOfferProduct_Product_ProductID	PK_Product_ProductID
	Production.TransactionHistory	FK_TransactionHistory_Product_ProductID	PK_Product_ProductID
	Production.WorkOrder	FK_WorkOrder_Product_ProductID	PK_Product_ProductID
	Production.BillOfMaterials	FK_BillOfMaterials_Product_ProductAssemblyID	PK_Product_ProductID
	Production.BillOfMaterials	FK_BillOfMaterials_Product_ComponentID	PK_Product_ProductID

Total: 14 table(s)

Referenced Tables

	Table	Foreign Key	Primary Key or Unique Constraint
	Production.ProductModel	FK_Product_ProductModel_ProductModelID	PK_ProductModel_ProductModelID
	Production.ProductSubcategory	FK_Product_ProductSubcategory_ProductSubcategoryID	PK_ProductSubcategory_ProductSubcategoryID
	Production.UnitMeasure	FK_Product_UnitMeasure_SizeUnitMeasureCode	PK_UnitMeasure_UnitMeasureCode
	Production.UnitMeasure	FK_Product_UnitMeasure_WeightUnitMeasureCode	PK_UnitMeasure_UnitMeasureCode

Total: 4 table(s)

Objects that [Production].[Product] depends on

	Object Name	Owner	Object Type	Dep Level
	Flag	dbo	User Defined type	1
	Name	dbo	User Defined type	1
	ProductCategory	Production	Table	2
	ProductModel	Production	Table	2
	UnitMeasure	Production	Table	2
	ProductSubcategory	Production	Table	3

Total: 6 object(s)

Objects that depend on [Production].[Product]

	Object Name	Owner	Object Type	Dep Level
	vProductAndDescription	Production	View	1
	BillOfMaterials	Production	Table	1
	ProductCostHistory	Production	Table	1
	ProductDocument	Production	Table	1
	ProductInventory	Production	Table	1
	ProductListPriceHistory	Production	Table	1
	ProductProductPhoto	Production	Table	1
	ProductReview	Production	Table	1
	ProductVendor	Purchasing	Table	1
	PurchaseOrderDetail	Purchasing	Table	1
	ShoppingCartItem	Sales	Table	1
	SpecialOfferProduct	Sales	Table	1
	TransactionHistory	Production	Table	1
	WorkOrder	Production	Table	1
	ufnGetProductDealerPrice	dbo	Function	2
	ufnGetProductListPrice	dbo	Function	2
	ufnGetProductStandardCost	dbo	Function	2
	ufnGetStock	dbo	Function	2
	SalesOrderDetail	Sales	Table	2
	WorkOrderRouting	Production	Table	2
	uspGetBillOfMaterials	dbo	Procedure	2
	uspGetWhereUsedProductID	dbo	Procedure	2
	iPurchaseOrderDetail	Purchasing	Trigger	2
	iWorkOrder	Production	Trigger	2
	uPurchaseOrderDetail	Purchasing	Trigger	2
	uWorkOrder	Production	Trigger	2
	iduSalesOrderDetail	Sales	Trigger	3

Total: 27 object(s)

SQL

CREATE TABLE [Product] (
    [ProductID] [int] IDENTITY (1, 1) NOT NULL ,
    [Name] [Name] NOT NULL ,
    [ProductNumber] [nvarchar] (25) COLLATE Latin1_General_CS_AS NOT NULL ,
    [MakeFlag] [Flag] NOT NULL CONSTRAINT [DF_Product_MakeFlag] DEFAULT ((1)),
    [FinishedGoodsFlag] [Flag] NOT NULL CONSTRAINT [DF_Product_FinishedGoodsFlag] DEFAULT ((1)),
    [Color] [nvarchar] (15) COLLATE Latin1_General_CS_AS NULL ,
    [SafetyStockLevel] [smallint] NOT NULL ,
    [ReorderPoint] [smallint] NOT NULL ,
    [StandardCost] [money] NOT NULL ,
    [ListPrice] [money] NOT NULL ,
    [Size] [nvarchar] (5) COLLATE Latin1_General_CS_AS NULL ,
    [SizeUnitMeasureCode] [nchar] (3) COLLATE Latin1_General_CS_AS NULL ,
    [WeightUnitMeasureCode] [nchar] (3) COLLATE Latin1_General_CS_AS NULL ,
    [Weight] [decimal](8, 2) NULL ,
    [DaysToManufacture] [int] NOT NULL ,
    [ProductLine] [nchar] (2) COLLATE Latin1_General_CS_AS NULL ,
    [Class] [nchar] (2) COLLATE Latin1_General_CS_AS NULL ,
    [Style] [nchar] (2) COLLATE Latin1_General_CS_AS NULL ,
    [ProductSubcategoryID] [int] NULL ,
    [ProductModelID] [int] NULL ,
    [SellStartDate] [datetime] NOT NULL ,
    [SellEndDate] [datetime] NULL ,
    [DiscontinuedDate] [datetime] NULL ,
    [rowguid]  uniqueidentifier ROWGUIDCOL  NOT NULL CONSTRAINT [DF_Product_rowguid] DEFAULT (newid()),
    [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Product_ModifiedDate] DEFAULT (getdate()),
    CONSTRAINT [PK_Product_ProductID] PRIMARY KEY  CLUSTERED
    (
        [ProductID]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_Product_ProductModel_ProductModelID] FOREIGN KEY
    (
        [ProductModelID]
    ) REFERENCES [ProductModel] (
        [ProductModelID]
    ),
    CONSTRAINT [FK_Product_ProductSubcategory_ProductSubcategoryID] FOREIGN KEY
    (
        [ProductSubcategoryID]
    ) REFERENCES [ProductSubcategory] (
        [ProductSubcategoryID]
    ),
    CONSTRAINT [FK_Product_UnitMeasure_SizeUnitMeasureCode] FOREIGN KEY
    (
        [SizeUnitMeasureCode]
    ) REFERENCES [UnitMeasure] (
        [UnitMeasureCode]
    ),
    CONSTRAINT [FK_Product_UnitMeasure_WeightUnitMeasureCode] FOREIGN KEY
    (
        [WeightUnitMeasureCode]
    ) REFERENCES [UnitMeasure] (
        [UnitMeasureCode]
    ),
    CONSTRAINT [CK_Product_Class] CHECK (upper([Class])='H' OR upper([Class])='M' OR upper([Class])='L' OR [Class] IS NULL),
    CONSTRAINT [CK_Product_DaysToManufacture] CHECK ([DaysToManufacture]>=(0)),
    CONSTRAINT [CK_Product_ListPrice] CHECK ([ListPrice]>=(0.00)),
    CONSTRAINT [CK_Product_ProductLine] CHECK (upper([ProductLine])='R' OR upper([ProductLine])='M' OR upper([ProductLine])='T' OR upper([ProductLine])='S' OR [ProductLine] IS NULL),
    CONSTRAINT [CK_Product_ReorderPoint] CHECK ([ReorderPoint]>(0)),
    CONSTRAINT [CK_Product_SafetyStockLevel] CHECK ([SafetyStockLevel]>(0)),
    CONSTRAINT [CK_Product_SellEndDate] CHECK ([SellEndDate]>=[SellStartDate] OR [SellEndDate] IS NULL),
    CONSTRAINT [CK_Product_StandardCost] CHECK ([StandardCost]>=(0.00)),
    CONSTRAINT [CK_Product_Style] CHECK (upper([Style])='U' OR upper([Style])='M' OR upper([Style])='W' OR [Style] IS NULL),
    CONSTRAINT [CK_Product_Weight] CHECK ([Weight]>(0.00))
) ON [PRIMARY]
GO

See Also

List of tables