Production.ProductModel Table

In This Topic

Description

Product model classification.

Properties

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

Columns

Column Name	Description	Datatype	Length	Default
ProductModelID	Primary key for ProductModel records.	Integer	4
Name	Product model description.	dbo.Name	50
CatalogDescription	Detailed product catalog information in xml format.	141	0
Instructions	Manufacturing instructions in xml format.	141	0
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_ProductModel_Name	Unique nonclustered index.
AK_ProductModel_rowguid	Unique nonclustered index. Used to support replication samples.
PK_ProductModel_ProductModelID	Primary key (clustered) constraint
PXML_ProductModel_CatalogDescription	Primary XML index.
PXML_ProductModel_Instructions	Primary XML index.

Relationships

Relationship	Description
FK_Product_ProductModel_ProductModelID	Foreign key constraint referencing ProductModel.ProductModelID.
FK_ProductModelIllustration_ProductModel_ProductModelID	Foreign key constraint referencing ProductModel.ProductModelID.
FK_ProductModelProductDescriptionCulture_ProductModel_ProductModelID	Foreign key constraint referencing ProductModel.ProductModelID.

Objects that depend on Production.ProductModel

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.	3
iWorkOrder	Trigger	AFTER INSERT trigger that inserts a row in the TransactionHistory table.	1
Production.Product	Table	Products sold or used in the manfacturing of sold products.	1
Production.ProductCostHistory	Table	Changes in the cost of a product over time.	2
Production.ProductDocument	Table	Cross-reference table mapping products to related product documents.	2
Production.ProductInventory	Table	Product inventory information.	2
Production.ProductListPriceHistory	Table	Changes in the list price of a product over time.	2
Production.ProductModelIllustration	Table	Cross-reference table mapping product models and illustrations.	1
Production.ProductModelProductDescriptionCulture	Table	Cross-reference table mapping product descriptions and the language the description is written in.	1
Production.ProductProductPhoto	Table	Cross-reference table mapping products and product photos.	2
Production.ProductReview	Table	Customer reviews of products they have purchased.	2
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.	2
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.	2
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.	2
dbo.ufnGetProductListPrice	User Defined Function	Scalar function returning the list price for a given product on a particular order date.	2
dbo.ufnGetProductStandardCost	User Defined Function	Scalar function returning the standard cost for a given product on a particular order date.	2
dbo.ufnGetStock	User Defined Function	Scalar function returning the quantity of inventory in LocationID 6 (Miscellaneous Storage)for a specified ProductID.	3
uPurchaseOrderDetail	Trigger	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in PurchaseOrderDetail and updates the PurchaseOrderHeader.SubTotal column.	3
dbo.uspGetBillOfMaterials	Stored Procedure	Stored procedure using a recursive query to return a multi-level bill of material for the specified ProductID.	2
dbo.uspGetWhereUsedProductID	Stored Procedure	Stored procedure using a recursive query to return all components or assemblies that directly or indirectly use the specified ProductID.	2
uWorkOrder	Trigger	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in the WorkOrder table.	1
Production.vProductAndDescription	View	Product names and descriptions. Product descriptions are provided in multiple languages.	2
Production.vProductModelCatalogDescription	View	Displays the content from each element in the xml column CatalogDescription for each product in the Production.ProductModel table that has catalog data.	1
Production.vProductModelInstructions	View	Displays the content from each element in the xml column Instructions for each product in the Production.ProductModel table that has manufacturing instructions.	1
Production.WorkOrder	Table	Manufacturing work orders.	1
Production.WorkOrderRouting	Table	Work order details.	1

Objects that Production.ProductModel depends on

	Database Object	Object Type	Description	Dep Level
	dbo.Name	User Defined Data Type		2

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductModel](
    [ProductModelID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [CatalogDescription] [xml](CONTENT [Production].[ProductDescriptionSchemaCollection]) NULL,
    [Instructions] [xml](CONTENT [Production].[ManuInstructionsSchemaCollection]) NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ProductModel_ProductModelID] PRIMARY KEY CLUSTERED 
(
    [ProductModelID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE [Production].[ProductModel] ADD  CONSTRAINT [DF_ProductModel_rowguid]  DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Production].[ProductModel] ADD  CONSTRAINT [DF_ProductModel_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]

Send comments on this topic.

Related Objects