AdventureWorks
| Table Name: | Production.Product |
| Description: | Products sold or used in the manfacturing of sold products. |
| Primary Keys: | ProductID |
| Field | Type | Defaults | Nulls? | Comments |
|---|---|---|---|---|
| ProductID | int - Identity | None | No | Primary key for Product records. |
| Name | nvarchar (50) | None | No | Name of the product. |
| ProductNumber | nvarchar (25) | None | No | Unique product identification number. |
| MakeFlag | bit | ((1)) | No | 0 = Product is purchased, 1 = Product is manufactured in-house. |
| FinishedGoodsFlag | bit | ((1)) | No | 0 = Product is not a salable item. 1 = Product is salable. |
| Color | nvarchar (15) | None | Yes | Product color. |
| SafetyStockLevel | smallint | None | No | Minimum inventory quantity. |
| ReorderPoint | smallint | None | No | Inventory level that triggers a purchase order or work order. |
| StandardCost | money | None | No | Standard cost of the product. |
| ListPrice | money | None | No | Selling price. |
| Size | nvarchar (5) | None | Yes | Product size. |
| SizeUnitMeasureCode | nchar (3) | None | Yes | Unit of measure for Size column. |
| WeightUnitMeasureCode | nchar (3) | None | Yes | Unit of measure for Weight column. |
| Weight | decimal (8, 2) | None | Yes | Product weight. |
| DaysToManufacture | int | None | No | Number of days required to manufacture the product. |
| ProductLine | nchar (2) | None | Yes | R = Road, M = Mountain, T = Touring, S = Standard |
| Class | nchar (2) | None | Yes | H = High, M = Medium, L = Low |
| Style | nchar (2) | None | Yes | W = Womens, M = Mens, U = Universal |
| ProductSubcategoryID | int | None | Yes | Product is a member of this product subcategory. Foreign key to ProductSubCategory.ProductSubCategoryID. |
| ProductModelID | int | None | Yes | Product is a member of this product model. Foreign key to ProductModel.ProductModelID. |
| SellStartDate | datetime | None | No | Date the product was available for sale. |
| SellEndDate | datetime | None | Yes | Date the product was no longer available for sale. |
| DiscontinuedDate | datetime | None | Yes | Date the product was discontinued. |
| rowguid | uniqueidentifier | (newid()) | No | ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample. |
| ModifiedDate | datetime | (getdate()) | No | Date and time the record was last updated. |
| Index | Clustered? | Unique? | Fields |
|---|---|---|---|
| PK_Product_ProductID | Yes | Yes | ProductID |
| AK_Product_ProductNumber | No | Yes | ProductNumber |
| AK_Product_Name | No | Yes | Name |
| AK_Product_rowguid | No | Yes | rowguid |
| Check Constraint | Text |
|---|---|
| CK_Product_SafetyStockLevel | ([SafetyStockLevel]>(0)) |
| CK_Product_ReorderPoint | ([ReorderPoint]>(0)) |
| CK_Product_StandardCost | ([StandardCost]>=(0.00)) |
| CK_Product_ListPrice | ([ListPrice]>=(0.00)) |
| CK_Product_Weight | ([Weight]>(0.00)) |
| CK_Product_DaysToManufacture | ([DaysToManufacture]>=(0)) |
| CK_Product_ProductLine | (upper([ProductLine])='R' OR upper([ProductLine])='M' OR upper([ProductLine])='T' OR upper([ProductLine])='S' OR [ProductLine] IS NULL) |
| CK_Product_Class | (upper([Class])='H' OR upper([Class])='M' OR upper([Class])='L' OR [Class] IS NULL) |
| CK_Product_Style | (upper([Style])='U' OR upper([Style])='M' OR upper([Style])='W' OR [Style] IS NULL) |
| CK_Product_SellEndDate | ([SellEndDate]>=[SellStartDate] OR [SellEndDate] IS NULL) |
| Internal Foreign Key Constraint | Affected Field | Source Table |
|---|---|---|
| FK_Product_ProductModel_ProductModelID | ProductModelID | Production.ProductModel |
| FK_Product_ProductSubcategory_ProductSubcategoryID | ProductSubcategoryID | Production.ProductSubcategory |
| FK_Product_UnitMeasure_SizeUnitMeasureCode | SizeUnitMeasureCode | Production.UnitMeasure |
| FK_Product_UnitMeasure_WeightUnitMeasureCode | WeightUnitMeasureCode | Production.UnitMeasure |
| Primary Key as Foreign Key Constraint | Affected Table | Affected Field |
|---|---|---|
| FK_BillOfMaterials_Product_ComponentID | Production.BillOfMaterials | ComponentID |
| FK_BillOfMaterials_Product_ProductAssemblyID | Production.BillOfMaterials | ProductAssemblyID |
| FK_ProductCostHistory_Product_ProductID | Production.ProductCostHistory | ProductID |
| FK_ProductDocument_Product_ProductID | Production.ProductDocument | ProductID |
| FK_ProductInventory_Product_ProductID | Production.ProductInventory | ProductID |
| FK_ProductListPriceHistory_Product_ProductID | Production.ProductListPriceHistory | ProductID |
| FK_ProductProductPhoto_Product_ProductID | Production.ProductProductPhoto | ProductID |
| FK_ProductReview_Product_ProductID | Production.ProductReview | ProductID |
| FK_ProductVendor_Product_ProductID | Purchasing.ProductVendor | ProductID |
| FK_PurchaseOrderDetail_Product_ProductID | Purchasing.PurchaseOrderDetail | ProductID |
| FK_ShoppingCartItem_Product_ProductID | Sales.ShoppingCartItem | ProductID |
| FK_SpecialOfferProduct_Product_ProductID | Sales.SpecialOfferProduct | ProductID |
| FK_TransactionHistory_Product_ProductID | Production.TransactionHistory | ProductID |
| FK_WorkOrder_Product_ProductID | Production.WorkOrder | ProductID |