You can simultaneously hold prices for List, Promotional, Distributor, Partner, & your cousin’s wife’s Aunt Billy. Just look at promotions as just another form of preferential pricing.
Split pricing into three elements: a price list header, price list detail and parts.
•Parts (SalableParts below) do not need to have an embedded price; they need to have an associated price.
•PriceLists is just what it says – the name and effective dates of a list of parts prices. End Users can give them a meaningful business name, you don’t care. And you can have upwards of a zillion of them (technically speaking) that can co-exist as the business needs.
•All PriceListParts does is associate a SalableParts record and a PriceLists record and carry a price and effective date range with a price.
Now, parts can be created by Bubba without any pricing decision, Pricelists can be managed by Daryll without any parts, and when everything is in place multiple prices can be assigned Sue to multiple pricelists for multiple parts.
For the business:
•A promotional campaign for a single part can change the price over time
•A promotional campaign can have different parts at different times
•Non-promotional pricing for Distributors & your cousin’s wife’s Aunt Billy are a gimme.
•You can easily print the List price, preferential price, & how much the buyer saved by buying from you on the invoice.
Caution:
Before you implement any effective date/time series structures, the business MUST decide how often changes can be made. What is the smallest amount of time an item can priced? One day, one minute, and one year requires different implementation.
The only time a PriceListParts record can be used is when the IsActive flag is true for the PriceListParts, and both associated SalableParts and PriceLists records. Since I am lazy, I created IsActive as a computed column based on function NowInInclusiveDateRange.
Ensure that for a given price list/part association in PriceListParts that there are no date range overlaps. Otherwise, your cousin’s wife’s Aunt Billy will have multiple prices in effect and available at the same time for blue turnip spoon tweezers.
Be sure to carefully consider the meaning of “changing a price” and what pricing history the business wants to maintain. Is it changing a current PriceListParts record due to incorrect entry, or is it actually adding a new PriceListParts record because there is a different price for a different span of time. Users will attempt to edit an existing record because it is easier, and the record of the prior price will have been destroyed
Structurally:
PriceLists 1 to n PriceListParts n to 1 SalableParts
create function [dbo].[NowInInclusiveDateRange]
(
@FromDate datetime,
@NowDate datetime,
@ToDate datetime
)
returns bit
as
begin
if @FromDate is null return 0
if @NowDate is null return 0
if @ToDate is null return 0
if @UseNow < @FromDate return 0
if @UseNow > @ToDate return 0
return 1
end
GO
CREATE TABLE [dbo].[PriceLists](
[PriceListId] [int] IDENTITY(1,1) NOT NULL,
[IsActive] AS ([dbo].[NowInInclusiveDayDateRange]([ActiveDate], getdate(), [InactiveDate])),
[ActiveDate] [datetime] NOT NULL,
[InactiveDate] [datetime] NOT NULL,
[PriceListName] [varchar](50) NOT NULL,
[Note] [varchar](1600) NULL,
[AddUTC] [datetime] NULL DEFAULT (getutcdate()),
[AddBy] [varchar](50) NOT NULL,
[ChangeUTC] [datetime] NULL,
[ChangeBy] [varchar](50) NULL,
CONSTRAINT [PK__PriceLists] PRIMARY KEY CLUSTERED
(
[PriceListId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PriceListParts](
[PriceListPartId] [int] IDENTITY(1,1) NOT NULL,
[PartNum] [varchar](15) NOT NULL,
[PriceListId] [int] NOT NULL,
[IsActive] AS ([dbo].[NowInInclusiveDayDateRange]([ActiveDate], getdate(), [InactiveDate])),
[UnitPrice] [money] NULL DEFAULT (0),
[ActiveDate] [datetime] NOT NULL,
[InactiveDate] [datetime] NOT NULL,
[Note] [varchar](4000) NULL,
[AddUTC] [datetime] NULL DEFAULT (getutcdate()),
[AddBy] [varchar](50) NOT NULL,
[ChangeUTC] [datetime] NULL,
[ChangeBy] [varchar](50) NULL,
CONSTRAINT [PK__PriceListParts] PRIMARY KEY CLUSTERED
(
[PriceListPartId] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SalableParts](
[PartNum] [varchar](15) NOT NULL,
[IsActive] AS ([dbo].[NowInInclusiveDayDateRange]([ActiveDate], getdate(), [InactiveDate])),
[ActiveDate] [datetime] NOT NULL,
[InactiveDate] [datetime] NULL DEFAULT (dateadd(year, 1, getdate())),
[UnitOfSale] [varchar](10) NOT NULL,
[Descrip] [varchar](400) NOT NULL,
[Note] [varchar](4000) NULL,
[AddUTC] [datetime] NULL DEFAULT (getutcdate()),
[AddBy] [varchar](50) NOT NULL,
[ChangeUTC] [datetime] NULL,
[ChangeBy] [varchar](50) NULL,
CONSTRAINT [PK__SalableParts] PRIMARY KEY CLUSTERED
(
[PartNum] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PriceListParts] WITH NOCHECK ADD CONSTRAINT [FK_PriceListParts_PriceLists] FOREIGN KEY([PriceListId])
REFERENCES [dbo].[PriceLists] ([PriceListId])
GO
ALTER TABLE [dbo].[PriceListParts] CHECK CONSTRAINT [FK_PriceListParts_PriceLists]
GO
ALTER TABLE [dbo].[PriceListParts] WITH NOCHECK ADD CONSTRAINT [FK_PriceListParts_SalableParts] FOREIGN KEY([PartNum])
REFERENCES [dbo].[SalableParts] ([PartNum])
GO
ALTER TABLE [dbo].[PriceListParts] CHECK CONSTRAINT [FK_PriceListParts_SalableParts]
GO