Table BillOfMaterials

Items required to make bicycles and bicycle subassemblies. It identifies the heirarchical relationship between a parent product and its components.

Indexes	Field Name	Data Type	Description
*	BillOfMaterialsID	int AUTOINCREMENT	Primary key for BillOfMaterials records.
	ProductAssemblyID	int	Parent product identification number. Foreign key to Product.ProductID.
*	ComponentID	int	Component identification number. Foreign key to Product.ProductID.
*	StartDate	datetime DEFAULT getdate()	Date the component started being used in the assembly item.
	EndDate	datetime	Date the component stopped being used in the assembly item.
*	UnitMeasureCode	nchar( 3 )	Standard code identifying the unit of measure for the quantity.
*	BOMLevel	smallint	Indicates the depth the component is from its parent (AssemblyID).
*	PerAssemblyQty	decimal( 8, 2 ) DEFAULT 1.00	Quantity of the component needed to create the assembly.
*	ModifiedDate	datetime DEFAULT getdate()	Date and time the record was last updated.
Indexes
	PK_BillOfMaterials_BillOfMaterialsID	ON BillOfMaterialsID	Nonclustered index created by a primary key constraint.
	AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate	ON ProductAssemblyID, ComponentID, StartDate	Clustered index.
	IX_BillOfMaterials_UnitMeasureCode	ON UnitMeasureCode	Nonclustered index.
Foreign Keys
	FK_BillOfMaterials_Product_ComponentID	( ComponentID ) ref Product (ProductID)	Foreign key constraint referencing Product.ComponentID.
	FK_BillOfMaterials_Product_ProductAssemblyID	( ProductAssemblyID ) ref Product (ProductID)	Foreign key constraint referencing Product.ProductAssemblyID.
	FK_BillOfMaterials_UnitMeasure_UnitMeasureCode	( UnitMeasureCode ) ref UnitMeasure (UnitMeasureCode)	Foreign key constraint referencing UnitMeasure.UnitMeasureCode.
Constraints
	CK_BillOfMaterials_ProductAssemblyID	[ProductAssemblyID]<>[ComponentID]
	CK_BillOfMaterials_BOMLevel	[ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1)
	CK_BillOfMaterials_ProductAssemblyID	[ProductAssemblyID]<>[ComponentID]
	CK_BillOfMaterials_EndDate	[EndDate]>[StartDate] OR [EndDate] IS NULL
	CK_BillOfMaterials_EndDate	[EndDate]>[StartDate] OR [EndDate] IS NULL
	CK_BillOfMaterials_BOMLevel	[ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1)
	CK_BillOfMaterials_BOMLevel	[ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1)
	CK_BillOfMaterials_PerAssemblyQty	[PerAssemblyQty]>=(1.00)

Table Culture

Lookup table containing the languages in which some AdventureWorks data is stored.

Indexes	Field Name	Data Type	Description
*	CultureID	nchar( 6 )	Primary key for Culture records.
*	Name	name	Culture description.
*	ModifiedDate	datetime DEFAULT getdate()	Date and time the record was last updated.
Indexes
	PK_Culture_CultureID	ON CultureID	Clustered index created by a primary key constraint.
	AK_Culture_Name	ON Name	Unique nonclustered index.

Table Document

Product maintenance documents.

Indexes	Field Name	Data Type	Description
*	DocumentNode	hierarchyid	Primary key for Document records.
	DocumentLevel	smallint	Depth in the document hierarchy.
*	Title	nvarchar( 50 )	Title of the document.
*	Owner	int	Employee who controls the document. Foreign key to Employee.BusinessEntityID
*	FolderFlag	bit DEFAULT 0	0 = This is a folder, 1 = This is a document.
*	FileName	nvarchar( 400 )	File name of the document
*	FileExtension	nvarchar( 8 )	File extension indicating the document type. For example, .doc or .txt.
*	Revision	nchar( 5 )	Revision number of the document.
*	ChangeNumber	int DEFAULT 0	Engineering change approval number.
*	Status	tinyint	1 = Pending approval, 2 = Approved, 3 = Obsolete
	DocumentSummary	nvarchar(max)	Document abstract.
	Document	varbinary(max)	Complete document.
*	rowguid	uniqueidentifier DEFAULT newid()	ROWGUIDCOL number uniquely identifying the record. Required for FileStream.
*	ModifiedDate	datetime DEFAULT getdate()	Date and time the record was last updated.
Indexes
	PK_Document_DocumentNode	ON DocumentNode	Clustered index created by a primary key constraint.
	AK_Document_DocumentLevel_DocumentNode	ON DocumentLevel, DocumentNode	Unique nonclustered index.
	UQ__Document__F73921F7C5112C2E	ON rowguid
	IX_Document_FileName_Revision	ON FileName, Revision	Unique nonclustered index.
Foreign Keys
	FK_Document_Employee_Owner	( Owner ) ref Employee (BusinessEntityID)	Foreign key constraint referencing Employee.BusinessEntityID.
Constraints
	CK_Document_Status	[Status]>=(1) AND [Status]<=(3)

Table Illustration

Bicycle assembly diagrams.

Indexes	Field Name	Data Type	Description
*	IllustrationID	int AUTOINCREMENT	Primary key for Illustration records.
	Diagram	xml	Illustrations used in manufacturing instructions. Stored as XML.
*	ModifiedDate	datetime DEFAULT getdate()	Date and time the record was last updated.
Indexes
	PK_Illustration_IllustrationID	ON IllustrationID	Clustered index created by a primary key constraint.

Table Location

Product inventory and manufacturing locations.

Indexes	Field Name	Data Type	Description
*	LocationID	smallint AUTOINCREMENT	Primary key for Location records.
*	Name	name	Location description.
*	CostRate	smallmoney DEFAULT 0.00	Standard hourly cost of the manufacturing location.
*	Availability	decimal( 8, 2 ) DEFAULT 0.00	Work capacity (in hours) of the manufacturing location.
*	ModifiedDate	datetime DEFAULT getdate()	Date and time the record was last updated.
Indexes
	PK_Location_LocationID	ON LocationID	Clustered index created by a primary key constraint.
	AK_Location_Name	ON Name	Unique nonclustered index.
Constraints
	CK_Location_CostRate	[CostRate]>=(0.00)
	CK_Location_Availability	[Availability]>=(0.00)

Table Product

Products sold or used in the manfacturing of sold products.

Indexes	Field Name	Data Type	Description
*	ProductID	int AUTOINCREMENT	Primary key for Product records.
*	Name	name	Name of the product.
*	ProductNumber	nvarchar( 25 )	Unique product identification number.
*	MakeFlag	flag DEFAULT 1	0 = Product is purchased, 1 = Product is manufactured in-house.
*	FinishedGoodsFlag	flag DEFAULT 1	0 = Product is not a salable item. 1 = Product is salable.
	Color	nvarchar( 15 )	Product color.
*	SafetyStockLevel	smallint	Minimum inventory quantity.
*	ReorderPoint	smallint	Inventory level that triggers a purchase order or work order.
*	StandardCost	money	Standard cost of the product.
*	ListPrice	money	Selling price.
	Size	nvarchar( 5 )	Product size.
	SizeUnitMeasureCode	nchar( 3 )	Unit of measure for Size column.
	WeightUnitMeasureCode	nchar( 3 )	Unit of measure for Weight column.
	Weight	decimal( 8, 2 )	Product weight.
*	DaysToManufacture	int	Number of days required to manufacture the product.
	ProductLine	nchar( 2 )	R = Road, M = Mountain, T = Touring, S = Standard
	Class	nchar( 2 )	H = High, M = Medium, L = Low
	Style	nchar( 2 )	W = Womens, M = Mens, U = Universal
	ProductSubcategoryID	int	Product is a member of this product subcategory. Foreign key to ProductSubCategory.ProductSubCategoryID.
	ProductModelID	int	Product is a member of this product model. Foreign key to ProductModel.ProductModelID.
*	SellStartDate	datetime	Date the product was available for sale.
	SellEndDate	datetime	Date the product was no longer available for sale.
	DiscontinuedDate	datetime	Date the product was discontinued.
*	rowguid	uniqueidentifier DEFAULT newid()	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
*	ModifiedDate	datetime DEFAULT getdate()	Date and time the record was last updated.
Indexes
	PK_Product_ProductID	ON ProductID	Clustered index created by a primary key constraint.
	AK_Product_Name	ON Name	Unique nonclustered index.
	AK_Product_ProductNumber	ON ProductNumber	Unique nonclustered index.
	AK_Product_rowguid	ON rowguid	Unique nonclustered index. Used to support replication samples.
Foreign Keys
	FK_Product_ProductModel_ProductModelID	( ProductModelID ) ref ProductModel (ProductModelID)	Foreign key constraint referencing ProductModel.ProductModelID.
	FK_Product_ProductSubcategory_ProductSubcategoryID	( ProductSubcategoryID ) ref ProductSubcategory (ProductSubcategoryID)	Foreign key constraint referencing ProductSubcategory.ProductSubcategoryID.
	FK_Product_UnitMeasure_SizeUnitMeasureCode	( SizeUnitMeasureCode ) ref UnitMeasure (UnitMeasureCode)	Foreign key constraint referencing UnitMeasure.UnitMeasureCode.
	FK_Product_UnitMeasure_WeightUnitMeasureCode	( WeightUnitMeasureCode ) ref UnitMeasure (UnitMeasureCode)	Foreign key constraint referencing UnitMeasure.UnitMeasureCode.
Constraints
	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
	CK_Product_SellEndDate	[SellEndDate]>=[SellStartDate] OR [SellEndDate] IS NULL

Table ProductCategory

High-level product categorization.

Indexes	Field Name	Data Type	Description
*	ProductCategoryID	int AUTOINCREMENT	Primary key for ProductCategory records.
*	Name	name	Category description.
*	rowguid	uniqueidentifier DEFAULT newid()	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
*	ModifiedDate	datetime DEFAULT getdate()	Date and time the record was last updated.
Indexes
	PK_ProductCategory_ProductCategoryID	ON ProductCategoryID	Clustered index created by a primary key constraint.
	AK_ProductCategory_Name	ON Name	Unique nonclustered index.
	AK_ProductCategory_rowguid	ON rowguid	Unique nonclustered index. Used to support replication samples.

Table ProductCostHistory

Changes in the cost of a product over time.

Indexes	Field Name	Data Type	Description
*	ProductID	int	Product identification number. Foreign key to Product.ProductID
*	StartDate	datetime	Product cost start date.
	EndDate	datetime	Product cost end date.
*	StandardCost	money	Standard cost of the product.
*	ModifiedDate	datetime DEFAULT getdate()	Date and time the record was last updated.
Indexes
	PK_ProductCostHistory_ProductID_StartDate	ON ProductID, StartDate	Clustered index created by a primary key constraint.
Foreign Keys
	FK_ProductCostHistory_Product_ProductID	( ProductID ) ref Product (ProductID)	Foreign key constraint referencing Product.ProductID.
Constraints
	CK_ProductCostHistory_EndDate	[EndDate]>=[StartDate] OR [EndDate] IS NULL
	CK_ProductCostHistory_EndDate	[EndDate]>=[StartDate] OR [EndDate] IS NULL
	CK_ProductCostHistory_StandardCost	[StandardCost]>=(0.00)

Table ProductDescription

Product descriptions in several languages.

Indexes	Field Name	Data Type	Description
*	ProductDescriptionID	int AUTOINCREMENT	Primary key for ProductDescription records.
*	Description	nvarchar( 400 )	Description of the product.
*	rowguid	uniqueidentifier DEFAULT newid()	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
*	ModifiedDate	datetime DEFAULT getdate()	Date and time the record was last updated.
Indexes
	PK_ProductDescription_ProductDescriptionID	ON ProductDescriptionID	Clustered index created by a primary key constraint.
	AK_ProductDescription_rowguid	ON rowguid	Unique nonclustered index. Used to support replication samples.

Table ProductDocument

Cross-reference table mapping products to related product documents.

Indexes	Field Name	Data Type	Description
*	ProductID	int	Product identification number. Foreign key to Product.ProductID.
*	DocumentNode	hierarchyid	Document identification number. Foreign key to Document.DocumentNode.
*	ModifiedDate	datetime DEFAULT getdate()	Date and time the record was last updated.
Indexes
	PK_ProductDocument_ProductID_DocumentNode	ON ProductID, DocumentNode	Clustered index created by a primary key constraint.
Foreign Keys
	FK_ProductDocument_Document_DocumentNode	( DocumentNode ) ref Document (DocumentNode)	Foreign key constraint referencing Document.DocumentNode.
	FK_ProductDocument_Product_ProductID	( ProductID ) ref Product (ProductID)	Foreign key constraint referencing Product.ProductID.

Table ProductInventory

Product inventory information.

Indexes	Field Name	Data Type	Description
*	ProductID	int	Product identification number. Foreign key to Product.ProductID.
*	LocationID	smallint	Inventory location identification number. Foreign key to Location.LocationID.
*	Shelf	nvarchar( 10 )	Storage compartment within an inventory location.
*	Bin	tinyint	Storage container on a shelf in an inventory location.
*	Quantity	smallint DEFAULT 0	Quantity of products in the inventory location.
*	rowguid	uniqueidentifier DEFAULT newid()	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
*	ModifiedDate	datetime DEFAULT getdate()	Date and time the record was last updated.
Indexes
	PK_ProductInventory_ProductID_LocationID	ON ProductID, LocationID	Clustered index created by a primary key constraint.
Foreign Keys
	FK_ProductInventory_Location_LocationID	( LocationID ) ref Location (LocationID)	Foreign key constraint referencing Location.LocationID.
	FK_ProductInventory_Product_ProductID	( ProductID ) ref Product (ProductID)	Foreign key constraint referencing Product.ProductID.
Constraints
	CK_ProductInventory_Shelf	[Shelf] like '[A-Za-z]' OR [Shelf]='N/A'
	CK_ProductInventory_Bin	[Bin]>=(0) AND [Bin]<=(100)

Table ProductListPriceHistory

Changes in the list price of a product over time.

Indexes	Field Name	Data Type	Description
*	ProductID	int	Product identification number. Foreign key to Product.ProductID
*	StartDate	datetime	List price start date.
	EndDate	datetime	List price end date
*	ListPrice	money	Product list price.
*	ModifiedDate	datetime DEFAULT getdate()	Date and time the record was last updated.
Indexes
	PK_ProductListPriceHistory_ProductID_StartDate	ON ProductID, StartDate	Clustered index created by a primary key constraint.
Foreign Keys
	FK_ProductListPriceHistory_Product_ProductID	( ProductID ) ref Product (ProductID)	Foreign key constraint referencing Product.ProductID.
Constraints
	CK_ProductListPriceHistory_EndDate	[EndDate]>=[StartDate] OR [EndDate] IS NULL
	CK_ProductListPriceHistory_EndDate	[EndDate]>=[StartDate] OR [EndDate] IS NULL
	CK_ProductListPriceHistory_ListPrice	[ListPrice]>(0.00)

Table ProductModel

Product model classification.

Indexes	Field Name	Data Type	Description
*	ProductModelID	int AUTOINCREMENT	Primary key for ProductModel records.
*	Name	name	Product model description.
	CatalogDescription	xml	Detailed product catalog information in xml format.
	Instructions	xml	Manufacturing instructions in xml format.
*	rowguid	uniqueidentifier DEFAULT newid()	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
*	ModifiedDate	datetime DEFAULT getdate()	Date and time the record was last updated.
Indexes
	PK_ProductModel_ProductModelID	ON ProductModelID	Clustered index created by a primary key constraint.
	AK_ProductModel_Name	ON Name	Unique nonclustered index.
	AK_ProductModel_rowguid	ON rowguid	Unique nonclustered index. Used to support replication samples.

Table ProductModelIllustration

Cross-reference table mapping product models and illustrations.

Indexes	Field Name	Data Type	Description
*	ProductModelID	int	Primary key. Foreign key to ProductModel.ProductModelID.
*	IllustrationID	int	Primary key. Foreign key to Illustration.IllustrationID.
*	ModifiedDate	datetime DEFAULT getdate()	Date and time the record was last updated.
Indexes
	PK_ProductModelIllustration_ProductModelID_IllustrationID	ON ProductModelID, IllustrationID	Clustered index created by a primary key constraint.
Foreign Keys
	FK_ProductModelIllustration_Illustration_IllustrationID	( IllustrationID ) ref Illustration (IllustrationID)	Foreign key constraint referencing Illustration.IllustrationID.
	FK_ProductModelIllustration_ProductModel_ProductModelID	( ProductModelID ) ref ProductModel (ProductModelID)	Foreign key constraint referencing ProductModel.ProductModelID.

Table ProductModelProductDescriptionCulture

Cross-reference table mapping product descriptions and the language the description is written in.

Indexes	Field Name	Data Type	Description
*	ProductModelID	int	Primary key. Foreign key to ProductModel.ProductModelID.
*	ProductDescriptionID	int	Primary key. Foreign key to ProductDescription.ProductDescriptionID.
*	CultureID	nchar( 6 )	Culture identification number. Foreign key to Culture.CultureID.
*	ModifiedDate	datetime DEFAULT getdate()	Date and time the record was last updated.
Indexes
	PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID	ON ProductModelID, ProductDescriptionID, CultureID	Clustered index created by a primary key constraint.
Foreign Keys
	FK_ProductModelProductDescriptionCulture_Culture_CultureID	( CultureID ) ref Culture (CultureID)	Foreign key constraint referencing Culture.CultureID.
	FK_ProductModelProductDescriptionCulture_ProductDescription_ProductDescriptionID	( ProductDescriptionID ) ref ProductDescription (ProductDescriptionID)	Foreign key constraint referencing ProductDescription.ProductDescriptionID.
	FK_ProductModelProductDescriptionCulture_ProductModel_ProductModelID	( ProductModelID ) ref ProductModel (ProductModelID)	Foreign key constraint referencing ProductModel.ProductModelID.

Table ProductPhoto

Product images.

Indexes	Field Name	Data Type	Description
*	ProductPhotoID	int AUTOINCREMENT	Primary key for ProductPhoto records.
	ThumbNailPhoto	varbinary(max)	Small image of the product.
	ThumbnailPhotoFileName	nvarchar( 50 )	Small image file name.
	LargePhoto	varbinary(max)	Large image of the product.
	LargePhotoFileName	nvarchar( 50 )	Large image file name.
*	ModifiedDate	datetime DEFAULT getdate()	Date and time the record was last updated.
Indexes
	PK_ProductPhoto_ProductPhotoID	ON ProductPhotoID	Clustered index created by a primary key constraint.

Table ProductProductPhoto

Cross-reference table mapping products and product photos.

Indexes	Field Name	Data Type	Description
*	ProductID	int	Product identification number. Foreign key to Product.ProductID.
*	ProductPhotoID	int	Product photo identification number. Foreign key to ProductPhoto.ProductPhotoID.
*	Primary	flag DEFAULT 0	0 = Photo is not the principal image. 1 = Photo is the principal image.
*	ModifiedDate	datetime DEFAULT getdate()	Date and time the record was last updated.
Indexes
	PK_ProductProductPhoto_ProductID_ProductPhotoID	ON ProductID, ProductPhotoID	Nonclustered index created by a primary key constraint.
Foreign Keys
	FK_ProductProductPhoto_Product_ProductID	( ProductID ) ref Product (ProductID)	Foreign key constraint referencing Product.ProductID.
	FK_ProductProductPhoto_ProductPhoto_ProductPhotoID	( ProductPhotoID ) ref ProductPhoto (ProductPhotoID)	Foreign key constraint referencing ProductPhoto.ProductPhotoID.

Table ProductReview

Customer reviews of products they have purchased.

Indexes	Field Name	Data Type	Description
*	ProductReviewID	int AUTOINCREMENT	Primary key for ProductReview records.
*	ProductID	int	Product identification number. Foreign key to Product.ProductID.
*	ReviewerName	name	Name of the reviewer.
*	ReviewDate	datetime DEFAULT getdate()	Date review was submitted.
*	EmailAddress	nvarchar( 50 )	Reviewer's e-mail address.
*	Rating	int	Product rating given by the reviewer. Scale is 1 to 5 with 5 as the highest rating.
	Comments	nvarchar( 3850 )	Reviewer's comments
*	ModifiedDate	datetime DEFAULT getdate()	Date and time the record was last updated.
Indexes
	PK_ProductReview_ProductReviewID	ON ProductReviewID	Clustered index created by a primary key constraint.
	IX_ProductReview_ProductID_Name	ON ProductID, ReviewerName	Nonclustered index.
Foreign Keys
	FK_ProductReview_Product_ProductID	( ProductID ) ref Product (ProductID)	Foreign key constraint referencing Product.ProductID.
Constraints
	CK_ProductReview_Rating	[Rating]>=(1) AND [Rating]<=(5)

Table ProductSubcategory

Product subcategories. See ProductCategory table.

Indexes	Field Name	Data Type	Description
*	ProductSubcategoryID	int AUTOINCREMENT	Primary key for ProductSubcategory records.
*	ProductCategoryID	int	Product category identification number. Foreign key to ProductCategory.ProductCategoryID.
*	Name	name	Subcategory description.
*	rowguid	uniqueidentifier DEFAULT newid()	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
*	ModifiedDate	datetime DEFAULT getdate()	Date and time the record was last updated.
Indexes
	PK_ProductSubcategory_ProductSubcategoryID	ON ProductSubcategoryID	Clustered index created by a primary key constraint.
	AK_ProductSubcategory_Name	ON Name	Unique nonclustered index.
	AK_ProductSubcategory_rowguid	ON rowguid	Unique nonclustered index. Used to support replication samples.
Foreign Keys
	FK_ProductSubcategory_ProductCategory_ProductCategoryID	( ProductCategoryID ) ref ProductCategory (ProductCategoryID)	Foreign key constraint referencing ProductCategory.ProductCategoryID.

Table ScrapReason

Manufacturing failure reasons lookup table.

Indexes	Field Name	Data Type	Description
*	ScrapReasonID	smallint AUTOINCREMENT	Primary key for ScrapReason records.
*	Name	name	Failure description.
*	ModifiedDate	datetime DEFAULT getdate()	Date and time the record was last updated.
Indexes
	PK_ScrapReason_ScrapReasonID	ON ScrapReasonID	Clustered index created by a primary key constraint.
	AK_ScrapReason_Name	ON Name	Unique nonclustered index.

Table TransactionHistory

Record of each purchase order, sales order, or work order transaction year to date.

Indexes	Field Name	Data Type	Description
*	TransactionID	int AUTOINCREMENT	Primary key for TransactionHistory records.
*	ProductID	int	Product identification number. Foreign key to Product.ProductID.
*	ReferenceOrderID	int	Purchase order, sales order, or work order identification number.
*	ReferenceOrderLineID	int DEFAULT 0	Line number associated with the purchase order, sales order, or work order.
*	TransactionDate	datetime DEFAULT getdate()	Date and time of the transaction.
*	TransactionType	nchar( 1 )	W = WorkOrder, S = SalesOrder, P = PurchaseOrder
*	Quantity	int	Product quantity.
*	ActualCost	money	Product cost.
*	ModifiedDate	datetime DEFAULT getdate()	Date and time the record was last updated.
Indexes
	PK_TransactionHistory_TransactionID	ON TransactionID	Clustered index created by a primary key constraint.
	IX_TransactionHistory_ProductID	ON ProductID	Nonclustered index.
	IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID	ON ReferenceOrderID, ReferenceOrderLineID	Nonclustered index.
Foreign Keys
	FK_TransactionHistory_Product_ProductID	( ProductID ) ref Product (ProductID)	Foreign key constraint referencing Product.ProductID.
Constraints
	CK_TransactionHistory_TransactionType	upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W'

Table TransactionHistoryArchive

Transactions for previous years.

Indexes	Field Name	Data Type	Description
*	TransactionID	int	Primary key for TransactionHistoryArchive records.
*	ProductID	int	Product identification number. Foreign key to Product.ProductID.
*	ReferenceOrderID	int	Purchase order, sales order, or work order identification number.
*	ReferenceOrderLineID	int DEFAULT 0	Line number associated with the purchase order, sales order, or work order.
*	TransactionDate	datetime DEFAULT getdate()	Date and time of the transaction.
*	TransactionType	nchar( 1 )	W = Work Order, S = Sales Order, P = Purchase Order
*	Quantity	int	Product quantity.
*	ActualCost	money	Product cost.
*	ModifiedDate	datetime DEFAULT getdate()	Date and time the record was last updated.
Indexes
	PK_TransactionHistoryArchive_TransactionID	ON TransactionID	Clustered index created by a primary key constraint.
	IX_TransactionHistoryArchive_ProductID	ON ProductID	Nonclustered index.
	IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID	ON ReferenceOrderID, ReferenceOrderLineID	Nonclustered index.
Constraints
	CK_TransactionHistoryArchive_TransactionType	upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W'

Table UnitMeasure

Unit of measure lookup table.

Indexes	Field Name	Data Type	Description
*	UnitMeasureCode	nchar( 3 )	Primary key.
*	Name	name	Unit of measure description.
*	ModifiedDate	datetime DEFAULT getdate()	Date and time the record was last updated.
Indexes
	PK_UnitMeasure_UnitMeasureCode	ON UnitMeasureCode	Clustered index created by a primary key constraint.
	AK_UnitMeasure_Name	ON Name	Unique nonclustered index.

Table WorkOrder

Manufacturing work orders.

Indexes	Field Name	Data Type	Description
*	WorkOrderID	int AUTOINCREMENT	Primary key for WorkOrder records.
*	ProductID	int	Product identification number. Foreign key to Product.ProductID.
*	OrderQty	int	Product quantity to build.
*	StockedQty	int	Quantity built and put in inventory.
*	ScrappedQty	smallint	Quantity that failed inspection.
*	StartDate	datetime	Work order start date.
	EndDate	datetime	Work order end date.
*	DueDate	datetime	Work order due date.
	ScrapReasonID	smallint	Reason for inspection failure.
*	ModifiedDate	datetime DEFAULT getdate()	Date and time the record was last updated.
Indexes
	PK_WorkOrder_WorkOrderID	ON WorkOrderID	Clustered index created by a primary key constraint.
	IX_WorkOrder_ProductID	ON ProductID	Nonclustered index.
	IX_WorkOrder_ScrapReasonID	ON ScrapReasonID	Nonclustered index.
Foreign Keys
	FK_WorkOrder_Product_ProductID	( ProductID ) ref Product (ProductID)	Foreign key constraint referencing Product.ProductID.
	FK_WorkOrder_ScrapReason_ScrapReasonID	( ScrapReasonID ) ref ScrapReason (ScrapReasonID)	Foreign key constraint referencing ScrapReason.ScrapReasonID.
Constraints
	CK_WorkOrder_OrderQty	[OrderQty]>(0)
	CK_WorkOrder_ScrappedQty	[ScrappedQty]>=(0)
	CK_WorkOrder_EndDate	[EndDate]>=[StartDate] OR [EndDate] IS NULL
	CK_WorkOrder_EndDate	[EndDate]>=[StartDate] OR [EndDate] IS NULL
Triggers
	iWorkOrder
	uWorkOrder

Table WorkOrderRouting

Work order details.

Indexes	Field Name	Data Type	Description
*	WorkOrderID	int	Primary key. Foreign key to WorkOrder.WorkOrderID.
*	ProductID	int	Primary key. Foreign key to Product.ProductID.
*	OperationSequence	smallint	Primary key. Indicates the manufacturing process sequence.
*	LocationID	smallint	Manufacturing location where the part is processed. Foreign key to Location.LocationID.
*	ScheduledStartDate	datetime	Planned manufacturing start date.
*	ScheduledEndDate	datetime	Planned manufacturing end date.
	ActualStartDate	datetime	Actual start date.
	ActualEndDate	datetime	Actual end date.
	ActualResourceHrs	decimal( 9, 4 )	Number of manufacturing hours used.
*	PlannedCost	money	Estimated manufacturing cost.
	ActualCost	money	Actual manufacturing cost.
*	ModifiedDate	datetime DEFAULT getdate()	Date and time the record was last updated.
Indexes
	PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence	ON WorkOrderID, ProductID, OperationSequence	Clustered index created by a primary key constraint.
	IX_WorkOrderRouting_ProductID	ON ProductID	Nonclustered index.
Foreign Keys
	FK_WorkOrderRouting_Location_LocationID	( LocationID ) ref Location (LocationID)	Foreign key constraint referencing Location.LocationID.
	FK_WorkOrderRouting_WorkOrder_WorkOrderID	( WorkOrderID ) ref WorkOrder (WorkOrderID)	Foreign key constraint referencing WorkOrder.WorkOrderID.
Constraints
	CK_WorkOrderRouting_ScheduledEndDate	[ScheduledEndDate]>=[ScheduledStartDate]
	CK_WorkOrderRouting_ScheduledEndDate	[ScheduledEndDate]>=[ScheduledStartDate]
	CK_WorkOrderRouting_ActualEndDate	[ActualEndDate]>=[ActualStartDate] OR [ActualEndDate] IS NULL OR [ActualStartDate] IS NULL
	CK_WorkOrderRouting_ActualEndDate	[ActualEndDate]>=[ActualStartDate] OR [ActualEndDate] IS NULL OR [ActualStartDate] IS NULL
	CK_WorkOrderRouting_ActualResourceHrs	[ActualResourceHrs]>=(0.0000)
	CK_WorkOrderRouting_PlannedCost	[PlannedCost]>(0.00)
	CK_WorkOrderRouting_ActualCost	[ActualCost]>(0.00)