Difference between revisions of "Production.Product (table)"
| Line 188: | Line 188: | ||
| '''Columns | | '''Columns | ||
| + | |- valign="top" | ||
| + | | AK_Product_Name | ||
| + | | Unique | ||
| + | | Name | ||
|- valign="top" | |- valign="top" | ||
| AK_Product_ProductNumber | | AK_Product_ProductNumber | ||
| Unique | | Unique | ||
| ProductNumber | | ProductNumber | ||
| − | |||
| − | |||
| − | |||
| − | |||
|- valign="top" | |- valign="top" | ||
| AK_Product_rowguid | | AK_Product_rowguid | ||
Latest revision as of 09:55, 27 August 2011
Contents
wikibot[edit]
| Table | Production.Product |
| Description | Products sold or used in the manfacturing of sold products. |
Columns[edit]
| 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[edit]
| Primary Key | Columns |
| PK_Product_ProductID | ProductID |
Indexes[edit]
| Index | Type | Columns |
| AK_Product_Name | Unique | Name |
| AK_Product_ProductNumber | Unique | ProductNumber |
| AK_Product_rowguid | Unique | rowguid |
Check Constraints[edit]
| 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[edit]
| Relation | Column | Referenced Column |
| Production.ProductModel | ProductModelID | ProductModelID |
| Production.ProductSubcategory | ProductSubcategoryID | ProductSubcategoryID |
| Production.UnitMeasure | SizeUnitMeasureCode | UnitMeasureCode |
| Production.UnitMeasure | WeightUnitMeasureCode | UnitMeasureCode |
Detail Tables[edit]
| 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[edit]
| Dependency Type | Object Type | Referenced Object |
| Data Type | Type | dbo.Flag |
| Data Type | Type | dbo.Name |
| Schema | Schema | Production |
Dependencies[edit]
| 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[edit]
| 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 |
