Table: Purchasing.ProductVendor

Properties

Owner:	Purchasing
Creation Date:	04/26/2006
Located On:	PRIMARY
Data Size KB:	40
Index Size KB:	48
Rows:	460
Description:	Cross-reference table mapping vendors with the products they supply.

Columns

Name	Data Type	Length	NULL	Default	IsIdentity	IsGUID	Description
ProductID	int	4					Primary key. Foreign key to Product.ProductID.
VendorID	int	4					Primary key. Foreign key to Vendor.VendorID.
AverageLeadTime	int	4					The average span of time (in days) between placing an order with the vendor and receiving the purchased product.
StandardPrice	money	8					The vendor's usual selling price.
LastReceiptCost	money	8					The selling price when last purchased.
LastReceiptDate	datetime	8					Date the product was last received by the vendor.
MinOrderQty	int	4					The maximum quantity that should be ordered.
MaxOrderQty	int	4					The minimum quantity that should be ordered.
OnOrderQty	int	4					The quantity currently on order.
UnitMeasureCode	nchar	3					The product's unit of measure.
ModifiedDate	datetime	8		(getdate())			Date and time the record was last updated.

Total: 11 column(s)

Indexes

Index	Primary	Unique	Description
PK_ProductVendor_ProductID_VendorID			Primary key (clustered) constraint
IX_ProductVendor_UnitMeasureCode			Nonclustered index.
IX_ProductVendor_VendorID			Nonclustered index.

Total: 3 index(es)

Check Constraints

	Name	Expression
	CK_ProductVendor_AverageLeadTime	([AverageLeadTime]>=(1))
	CK_ProductVendor_LastReceiptCost	([LastReceiptCost]>(0.00))
	CK_ProductVendor_MaxOrderQty	([MaxOrderQty]>=(1))
	CK_ProductVendor_MinOrderQty	([MinOrderQty]>=(1))
	CK_ProductVendor_OnOrderQty	([OnOrderQty]>=(0))
	CK_ProductVendor_StandardPrice	([StandardPrice]>(0.00))

Total: 6 constraint(s)

Referenced Tables

Table	Foreign Key	Primary Key or Unique Constraint
Production.Product	FK_ProductVendor_Product_ProductID	PK_Product_ProductID
Production.UnitMeasure	FK_ProductVendor_UnitMeasure_UnitMeasureCode	PK_UnitMeasure_UnitMeasureCode
Purchasing.Vendor	FK_ProductVendor_Vendor_VendorID	PK_Vendor_VendorID

Total: 3 table(s)

Objects that [Purchasing].[ProductVendor] depends on

Object Name	Owner	Object Type	Dep Level
AccountNumber	dbo	User Defined type	1
Flag	dbo	User Defined type	1
Name	dbo	User Defined type	1
ProductCategory	Production	Table	2
ProductModel	Production	Table	2
UnitMeasure	Production	Table	2
Vendor	Purchasing	Table	2
ProductSubcategory	Production	Table	3
Product	Production	Table	4

Total: 9 object(s)

SQL

CREATE TABLE [ProductVendor] (
    [ProductID] [int] NOT NULL ,
    [VendorID] [int] NOT NULL ,
    [AverageLeadTime] [int] NOT NULL ,
    [StandardPrice] [money] NOT NULL ,
    [LastReceiptCost] [money] NULL ,
    [LastReceiptDate] [datetime] NULL ,
    [MinOrderQty] [int] NOT NULL ,
    [MaxOrderQty] [int] NOT NULL ,
    [OnOrderQty] [int] NULL ,
    [UnitMeasureCode] [nchar] (3) COLLATE Latin1_General_CS_AS NOT NULL ,
    [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_ProductVendor_ModifiedDate] DEFAULT (getdate()),
    CONSTRAINT [PK_ProductVendor_ProductID_VendorID] PRIMARY KEY  CLUSTERED
    (
        [ProductID],
        [VendorID]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_ProductVendor_Product_ProductID] FOREIGN KEY
    (
        [ProductID]
    ) REFERENCES [Product] (
        [ProductID]
    ),
    CONSTRAINT [FK_ProductVendor_UnitMeasure_UnitMeasureCode] FOREIGN KEY
    (
        [UnitMeasureCode]
    ) REFERENCES [UnitMeasure] (
        [UnitMeasureCode]
    ),
    CONSTRAINT [FK_ProductVendor_Vendor_VendorID] FOREIGN KEY
    (
        [VendorID]
    ) REFERENCES [Vendor] (
        [VendorID]
    ),
    CONSTRAINT [CK_ProductVendor_AverageLeadTime] CHECK ([AverageLeadTime]>=(1)),
    CONSTRAINT [CK_ProductVendor_LastReceiptCost] CHECK ([LastReceiptCost]>(0.00)),
    CONSTRAINT [CK_ProductVendor_MaxOrderQty] CHECK ([MaxOrderQty]>=(1)),
    CONSTRAINT [CK_ProductVendor_MinOrderQty] CHECK ([MinOrderQty]>=(1)),
    CONSTRAINT [CK_ProductVendor_OnOrderQty] CHECK ([OnOrderQty]>=(0)),
    CONSTRAINT [CK_ProductVendor_StandardPrice] CHECK ([StandardPrice]>(0.00))
) ON [PRIMARY]
GO