Production.TransactionHistory Table

Information

Name	TransactionHistory
Schema	Production
Row Count	0
Data Size
Index Size
Reserved Size
Unused Size
Created	29.3.2010. 21:58:18
Modified	29.3.2010. 21:58:55

Extended Properties

Name	Type	Property Name	Value
TransactionHistory	TABLE	MS_Description	Record of each purchase order, sales order, or work order transaction year to date.

Columns

Name	Data Type	Collation	References	Default
TransactionID	int
ProductID	int		Production.Product.ProductID
ReferenceOrderID	int
ReferenceOrderLineID	int			((0))
TransactionDate	datetime			(getdate())
TransactionType	nchar(1)	SQL_Latin1_General_CP1_CI_AS
Quantity	int
ActualCost	money
ModifiedDate	datetime			(getdate())

Indexes

Name	Type	Columns
PK_TransactionHistory_TransactionID	Clustered	TransactionID
IX_TransactionHistory_ProductID	NonClustered	ProductID
IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID	NonClustered	ReferenceOrderID, ReferenceOrderLineID

Check Constraints

Name	Column	Enabled	Trusted	Expression
CK_TransactionHistory_TransactionType	TransactionType			(upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W')

Foreign Key Constraints

Name	Type	Referenced Table
FK_TransactionHistory_Product_ProductID	Foreign key	Production.Product

SQL Script

CREATE TABLE [Production].[TransactionHistory] (
    [TransactionID] int NOT NULL IDENTITY,
    [ProductID] int NOT NULL,
    [ReferenceOrderID] int NOT NULL,
    [ReferenceOrderLineID] int NOT NULL DEFAULT ((0)),
    [TransactionDate] datetime NOT NULL DEFAULT (getdate()),
    [TransactionType] nchar(1) NOT NULL,
    [Quantity] int NOT NULL,
    [ActualCost] money NOT NULL,
    [ModifiedDate] datetime NOT NULL DEFAULT (getdate())
);
CREATE INDEX [IX_TransactionHistory_ProductID] on [Production].[TransactionHistory]([ProductID]);
CREATE INDEX [IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID] on [Production].[TransactionHistory]([ReferenceOrderID], [ReferenceOrderLineID]);