Production Product (table)

wikibot

TableProduction.Product
DescriptionProducts sold or used in the manfacturing of sold products.

Columns

ColumnData TypeNullableDefaultDescription
ProductIDintnot null Primary key for Product records.
Namedbo.Namenot null Name of the product.
ProductNumbernvarchar(25)not null Unique product identification number.
MakeFlagdbo.Flagnot null((1))0 = Product is purchased, 1 = Product is manufactured in-house.
FinishedGoodsFlagdbo.Flagnot null((1))0 = Product is not a salable item. 1 = Product is salable.
Colornvarchar(15)null Product color.
SafetyStockLevelsmallintnot null Minimum inventory quantity.
ReorderPointsmallintnot null Inventory level that triggers a purchase order or work order.
StandardCostmoneynot null Standard cost of the product.
ListPricemoneynot null Selling price.
Sizenvarchar(5)null Product size.
SizeUnitMeasureCodenchar(3)null Unit of measure for Size column.
WeightUnitMeasureCodenchar(3)null Unit of measure for Weight column.
Weightdecimal(8, 2)null Product weight.
DaysToManufactureintnot null Number of days required to manufacture the product.
ProductLinenchar(2)null R = Road, M = Mountain, T = Touring, S = Standard
Classnchar(2)null H = High, M = Medium, L = Low
Stylenchar(2)null W = Womens, M = Mens, U = Universal
ProductSubcategoryIDintnull Product is a member of this product subcategory. Foreign key to ProductSubCategory.ProductSubCategoryID.
ProductModelIDintnull Product is a member of this product model. Foreign key to ProductModel.ProductModelID.
SellStartDatedatetimenot null Date the product was available for sale.
SellEndDatedatetimenull Date the product was no longer available for sale.
DiscontinuedDatedatetimenull Date the product was discontinued.
rowguiduniqueidentifiernot null(newid())ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDatedatetimenot null(getdate())Date and time the record was last updated.

Primary Key

Primary KeyColumns
PK_Product_ProductIDProductID

Indexes

IndexTypeColumns
AK_Product_NameUniqueName
AK_Product_ProductNumberUniqueProductNumber
AK_Product_rowguidUniquerowguid

Check Constraints

Check ConstraintExpressionDescription
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

RelationColumnReferenced Column
Production.ProductModelProductModelIDProductModelID
Production.ProductSubcategoryProductSubcategoryIDProductSubcategoryID
Production.UnitMeasureSizeUnitMeasureCodeUnitMeasureCode
Production.UnitMeasureWeightUnitMeasureCodeUnitMeasureCode

Detail Tables

Detail TableColumnReferencing Column
Production.BillOfMaterialsProductIDComponentID
Production.BillOfMaterialsProductIDProductAssemblyID
Production.ProductCostHistoryProductIDProductID
Production.ProductDocumentProductIDProductID
Production.ProductInventoryProductIDProductID
Production.ProductListPriceHistoryProductIDProductID
Production.ProductProductPhotoProductIDProductID
Production.ProductReviewProductIDProductID
Purchasing.ProductVendorProductIDProductID
Purchasing.PurchaseOrderDetailProductIDProductID
Sales.ShoppingCartItemProductIDProductID
Sales.SpecialOfferProductProductIDProductID
Production.TransactionHistoryProductIDProductID
Production.WorkOrderProductIDProductID

References

Dependency TypeObject TypeReferenced Object
Data TypeTypedbo.Flag
Data TypeTypedbo.Name
SchemaSchemaProduction

Dependencies

Reference TypeObject TypeReferencing Object
SelectViewProduction.vProductAndDescription
SelectProceduredbo.uspGetBillOfMaterials
SelectProceduredbo.uspGetWhereUsedProductID
SelectFunctiondbo.ufnGetProductDealerPrice
SelectFunctiondbo.ufnGetProductListPrice
SelectFunctiondbo.ufnGetProductStandardCost