Sales.SpecialDeals

Columns

• //

  ${escapify(column.description)}
  SpecialDealID
  [int] NOT NULL DEFAULT (NEXT VALUE FOR [Sequences].[SpecialDealID])
• //

  ${escapify(column.description)}
  StockItemID
  [int] NULL
• //

  ${escapify(column.description)}
  CustomerID
  [int] NULL
• //

  ${escapify(column.description)}
  BuyingGroupID
  [int] NULL
• //

  ${escapify(column.description)}
  CustomerCategoryID
  [int] NULL
• //

  ${escapify(column.description)}
  StockGroupID
  [int] NULL
• //

  ${escapify(column.description)}
  DealDescription
  [nvarchar](30) NOT NULL
• //

  ${escapify(column.description)}
  StartDate
  [date] NOT NULL
• //

  ${escapify(column.description)}
  EndDate
  [date] NOT NULL
• //

  ${escapify(column.description)}
  DiscountAmount
  [decimal](18, 2) NULL
• //

  ${escapify(column.description)}
  DiscountPercentage
  [decimal](18, 3) NULL
• //

  ${escapify(column.description)}
  UnitPrice
  [decimal](18, 2) NULL
• LastEditedBy
  [int] NOT NULL
• LastEditedWhen
  [datetime2](8) NOT NULL DEFAULT (sysdatetime())

Indexes

• PK_Sales_SpecialDeals
  UNIQUE CLUSTERED
  SpecialDealID
• //

  Auto-created to support a foreign key
  FK_Sales_SpecialDeals_StockItemID
  NONCLUSTERED
  StockItemID
• //

  Auto-created to support a foreign key
  FK_Sales_SpecialDeals_CustomerID
  NONCLUSTERED
  CustomerID
• //

  Auto-created to support a foreign key
  FK_Sales_SpecialDeals_BuyingGroupID
  NONCLUSTERED
  BuyingGroupID
• //

  Auto-created to support a foreign key
  FK_Sales_SpecialDeals_CustomerCategoryID
  NONCLUSTERED
  CustomerCategoryID
• //

  Auto-created to support a foreign key
  FK_Sales_SpecialDeals_StockGroupID
  NONCLUSTERED
  StockGroupID

Foreign Keys

• FK_Sales_SpecialDeals_Application_People
  Application.People
  LastEditedBy ⥱ PersonID
• FK_Sales_SpecialDeals_BuyingGroupID_Sales_BuyingGroups
  Sales.BuyingGroups
  BuyingGroupID ⥱ BuyingGroupID
• FK_Sales_SpecialDeals_CustomerCategoryID_Sales_CustomerCategories
  Sales.CustomerCategories
  CustomerCategoryID ⥱ CustomerCategoryID
• FK_Sales_SpecialDeals_CustomerID_Sales_Customers
  Sales.Customers
  CustomerID ⥱ CustomerID
• FK_Sales_SpecialDeals_StockGroupID_Warehouse_StockGroups
  Warehouse.StockGroups
  StockGroupID ⥱ StockGroupID
• FK_Sales_SpecialDeals_StockItemID_Warehouse_StockItems
  Warehouse.StockItems
  StockItemID ⥱ StockItemID

Check Constraints

• //

  Ensures that each special price row contains one and only one of DiscountAmount, DiscountPercentage, and UnitPrice
  CK_Sales_SpecialDeals_Exactly_One_NOT_NULL_Pricing_Option_Is_Required
  (((case when [DiscountAmount] IS NULL then (0) else (1) end+case when [DiscountPercentage] IS NULL then (0) else (1) end)+case when [UnitPrice] IS NULL then (0) else (1) end)=(1))
• //

  Ensures that if a specific price is allocated that it applies to a specific stock item
  CK_Sales_SpecialDeals_Unit_Price_Deal_Requires_Special_StockItem
  ([StockItemID] IS NOT NULL AND [UnitPrice] IS NOT NULL OR [UnitPrice] IS NULL)

Sample Data