Production.ProductSubcategory Table

Description

Product subcategories. See ProductCategory table.

Properties

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

Columns

Column Name	Description	Datatype	Length	Default
ProductSubcategoryID	Primary key for ProductSubcategory records.	Integer	4
ProductCategoryID	Product category identification number. Foreign key to ProductCategory.ProductCategoryID.	Integer	4
Name	Subcategory description.	dbo.Name	50
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_ProductSubcategory_Name	Unique nonclustered index.
AK_ProductSubcategory_rowguid	Unique nonclustered index. Used to support replication samples.
PK_ProductSubcategory_ProductSubcategoryID	Primary key (clustered) constraint

Relationships

Relationship	Description
FK_Product_ProductSubcategory_ProductSubcategoryID	Foreign key constraint referencing ProductSubcategory.ProductSubcategoryID.
FK_ProductSubcategory_ProductCategory_ProductCategoryID	Foreign key constraint referencing ProductCategory.ProductCategoryID.

Objects that depend on Production.ProductSubcategory

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.	2
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.	4
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.	3
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.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.	2
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.	3
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.	2
Production.TransactionHistory	Table	Record of each purchase order, sales order, or work order transaction year to date.	2
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.	3
Production.vProductAndDescription	View	Product names and descriptions. Product descriptions are provided in multiple languages.	2
Production.WorkOrder	Table	Manufacturing work orders.	2
Production.WorkOrderRouting	Table	Work order details.	3

Objects that Production.ProductSubcategory depends on

	Database Object	Object Type	Description	Dep Level
	dbo.Name	User Defined Data Type		1
	Production.ProductCategory	Table	High-level product categorization.	1

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductSubcategory](
    [ProductSubcategoryID] [int] IDENTITY(1,1) NOT NULL,
    [ProductCategoryID] [int] NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ProductSubcategory_ProductSubcategoryID] PRIMARY KEY CLUSTERED 
(
    [ProductSubcategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [Production].[ProductSubcategory] ADD  CONSTRAINT [DF_ProductSubcategory_rowguid]  DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Production].[ProductSubcategory] ADD  CONSTRAINT [DF_ProductSubcategory_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [Production].[ProductSubcategory]  WITH CHECK ADD  CONSTRAINT [FK_ProductSubcategory_ProductCategory_ProductCategoryID] FOREIGN KEY([ProductCategoryID])
REFERENCES [Production].[ProductCategory] ([ProductCategoryID])
ALTER TABLE [Production].[ProductSubcategory] CHECK CONSTRAINT [FK_ProductSubcategory_ProductCategory_ProductCategoryID]

Related Objects