InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 05:04:28 GMT20http://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspx[quote][b]Jason Whitney (10/1/2009)[/b][hr]I have run into this before, and the article gives the first half of the solution. The second part is what I can't figure out; how to avoid overlapping time-frames.The supplied solution doesn't enforce data integrity for overlapping dates. For the illustration given it may not need to, but for many applications these date ranges must be mutually exclusive. For example a patient comes in to the hospital as an outpatient (O) and has a complication and gets admitted as an inpatient (I). The data is temporal, but the dates/times cannot overlap. How would I model this?The only solution I have been able to come up with is a new data type for interval data. (Hopefully we will see this in the next version of SQL) I tried a CLR, but I was never able to get it to work quite right. My database has failed me! :crazy:[/quote]The safest solution is to use triggers. The next safest solution is to use stored procs. With triggers you would look for an overlap in the inserted table in comparison to the existing table. Something like:[code="sql"]If Exists( Select * From dbo.PromotedProducts As PP Join inserted As I On I.PromotionId = PP.PromotionId And I.ProductId = PP.ProductId And I.Start <= PP.EndDate And I.EndDate >= PP.StartDate ) Raiserror(...[/code]Mon, 12 Oct 2009 23:26:26 GMTThomas-282729http://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspxUnfortunately, the article glossed over quite a bit. The Devil in these details is a much bigger beast. For example, including the effective dates as part of the primary key will not prevent duplicates as others have mentioned. Granted, we cannot see check constraints or triggers in his design, but one could image data such as:PromotionId, ProductId, Start, End1, 1, 1/1/2009, 1/31/2009 5:00 PM1, 1, 1/1/2009, 1/31/2009 11:59 PM1, 1, 1/2/2009, 1/31/2009 11:59 PMI have dealt with temporal designs in the past and in one case a design that required *two* sets of dates on numerous data elements. The single greatest headache is that adding temporal fields will break normal data integrity rules such as the intent of primary keys as above mentioned. In order to prevent having two active promotions for the same product and promotion, you must use triggers to enforce data integrity rules which account for overlap.A temporal design most definitely is the right fit in certain circumstances but it should be clear that it must be a decision in which the stakeholders account for the additional development cost. That cost rears its head in every query (and thus report) against the data.Temporal designs can get quite complicated fast. For example, I can see a requirement for an effective date range for both the promotion and the product's membership to the promotion. "This promotion is only for June. Product X will not be available as part of that promotion until June 15 due to supplier issues." etc.Mon, 12 Oct 2009 23:19:34 GMTThomas-282729http://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspxYou 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 [b]MUST [/b]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 [u]no date range overlaps[/u]. 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 [u][b]will [/b][/u]attempt to edit an existing record because it is easier, and the record of the prior price will have been destroyedStructurally: [b]PriceLists[/b] 1 to n [b]PriceListParts[/b] n to 1 [b]SalableParts[/b]create function [dbo].[NowInInclusiveDateRange] ( @FromDate datetime, @NowDate datetime, @ToDate datetime ) returns bitas 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 1endGOCREATE 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]GOCREATE 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]GOCREATE 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]GOALTER TABLE [dbo].[PriceListParts] WITH NOCHECK ADD CONSTRAINT [FK_PriceListParts_PriceLists] FOREIGN KEY([PriceListId])REFERENCES [dbo].[PriceLists] ([PriceListId])GOALTER TABLE [dbo].[PriceListParts] CHECK CONSTRAINT [FK_PriceListParts_PriceLists]GOALTER TABLE [dbo].[PriceListParts] WITH NOCHECK ADD CONSTRAINT [FK_PriceListParts_SalableParts] FOREIGN KEY([PartNum])REFERENCES [dbo].[SalableParts] ([PartNum])GOALTER TABLE [dbo].[PriceListParts] CHECK CONSTRAINT [FK_PriceListParts_SalableParts]GOTue, 06 Oct 2009 12:44:46 GMTBC Featherstonehttp://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspx[quote][b]Nadrek (10/2/2009)[/b][hr][Database level constraints are the best way I know of to limit issues from happening; whether they're function based and expensive [...][/quote]Agreed, and there have been comments woven through the thread so far, that effective dates are expensive, ditto for any constraint that is difficult to suborn.If commercial reality, or in other words, keeping your job, means taking shortcuts, then it's up to the DBA to recommend spending the immediately available budget on the lower total cost constraints and document the exposure involved in not implementing the higher total cost constraints. It's then up to the CxO's to determine the total cost to the business of not implementing the higher total cost constraints, and carry the responsibility for the risk. If you have no authority for the budget, you have no responsibility for those extraneous costs, after you've made the recommendations that fall within generally agreed best practice. But then "generally agreed best practice" is a whole other can of worms.Back on to topic, here's some fragments that I exhumed from the archive of one of my succesful SQL2000 projects. Yes, I have had my share of unsuccessful projects. Doh.As I don't write nearly as well, nor as clearly, as Sam Bendayan, this response from here on, isn't pitched to those fairly new to the industry, only to those that won't hesitate to tear me to shreds, eh REBAR. The gist of this project is that a "record" is represented by a [DomainId], the temporally unique aspect of the record is represented by the [Id], within the [DomainId]. There were a huge number of auditability constraints placed on the project. In order to work with the auditability and the outrageous expense of temporal consistancy at any -specified- point in time, not just now, the logic was based on shortest path for most frequently used requests, hence the exception logic may be drawn out and regardless of that, a -substantial- amount of horsepower was in use to run the production systems. The five fragments are for a CREATE TABLE, the INSTEAD OF INSERT trigger, the INSTEAD OF UPDATE trigger, a SET procedure to do INSERT, UPDATE and DELETE, and a GET scalar procedure. These should give a pretty good overview of how the temporal management aspects of the project came together, without worrying about the auditability and reporting, which moved out of SQL and into web services and proprietary products.Horses, for courses, as long as -all- the data is there, and the integrity is ensured, I have no problem with positively bizarre business rules being implemented for RO off the database and on the application servers.The project database stored batched transactional data from various revenue generating machines, a CRM structure for multiple companies owning subsets of the machines, which were occasionally sold between organisations (no, no, no, I did not want to do this), and batched OLAP denormalisations where the DBMS simply could not cope with a report structure across the temporal and organisational structure.Some of what I did with this project, I would love to redo with SQL2008, particularly using the advances in DATE and TIME handling and using CLR, although I still have a bad feeling about some of the performance issues in that area.As this code was dug off CD and isn't recent production code, any errors are entirely mine, incurred during coding, and I should be lambasted for them.[code="sql"]/* ------------------------------------------------------------------- Table MachineDomain Documentation Project: thisProject Aspect: Core Prepared by: thisOrganisationalUnit For: thatOrganistionalUnit Description Stores the descriptor for each machine to be supported by the suite. The [Serial] column is an alias for the [Id] and is a unique serial number from the manufacturer. Join to the record on the [Id], not the [Serial]. -----------------------------------------------------------------*/CREATE TABLE [dbo].[MachineDomain]( [Active] [datetime] NULL, [Archive] [datetime] NULL, [ArchiveInsert] [datetime] NULL, [AuditReferenceInsert] [uniqueidentifier] NULL, [AuditReferenceUpdate] [uniqueidentifier] NULL, [Description] [nvarchar] (900) NULL, [DomainId] [int] NOT NULL IDENTITY(1, 1), [Id] [int] NULL, [ModelCategoryDomainId] [int] NOT NULL, [Name] [nvarchar] (450) NOT NULL, [Serial] [nchar] (24) NULL ) ON [PRIMARY]GO[/code] [code="sql"] /* ------------------------------------------------------------------- Trigger MachineDomainInsteadOfInsert Documentation Project: thisProject Aspect: Core Prepared by: thisOrganisationalUnit For: thatOrganistionalUnit -----------------------------------------------------------------*/ALTER TRIGGER [MachineDomainInsteadOfInsert] ON [dbo].[MachineDomain] INSTEAD OF INSERT ASBEGIN SET NOCOUNT ON DECLARE @Active [datetime] DECLARE @AccountId [int] DECLARE @Archive [datetime] DECLARE @AuditCategoryId [int] DECLARE @AuditReferenceInsert [uniqueidentifier] DECLARE @AuditReferenceUpdate [uniqueidentifier] DECLARE @Count [int] DECLARE @CurrentTimestamp [datetime] DECLARE @DomainId [int] DECLARE @Error [int] DECLARE @MaximumTimestamp [datetime] DECLARE @MessageAlias [nchar] (24) DECLARE @MessageObject [nvarchar] (450) DECLARE @MessageText [nvarchar] (400) DECLARE @Return [int]/* ------------------------------------------------------------------- Get constants. ------------------------------------------------------------------- */ SET @MessageObject = LOWER(N'['+@@SERVERNAME+N'].['+DB_NAME()+N'].['+SESSION_USER+N'].['+OBJECT_NAME(@@PROCID)+N']')/* ------------------------------------------------------------------- INSERT multiple records in a single batch is not supported.------------------------------------------------------------------- */ SELECT @Count = COUNT(*) FROM INSERTED SET @Error = @@ERROR IF (@Error <> 0) BEGIN SET @MessageAlias = N'TOU56301' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) RAISERROR (@MessageText,10,1) WITH SETERROR RETURN END IF (@Count = 0) BEGIN SET @MessageAlias = N'TOU56302' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) RAISERROR (@MessageText,10,1) WITH SETERROR RETURN END IF (@Count > 1) BEGIN SET @MessageAlias = N'TOU56303' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) RAISERROR (@MessageText,10,1) WITH SETERROR RETURN END /* ------------------------------------------------------------------- Get values from the INSERTED table that will be referenced several times in the following checking ------------------------------------------------------------------- */ SET @Active = NULL SET @Archive = NULL SET @AuditReferenceInsert = NULL SELECT @Active = [Active], @Archive = [Archive], @AuditReferenceInsert = [AuditReferenceInsert] FROM INSERTED SET @Error = @@ERROR IF (@Error <> 0) BEGIN SET @MessageAlias = N'TOU56304' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) RAISERROR (@MessageText,10,1) WITH SETERROR RETURN END /* ------------------------------------------------------------------- Check the session token is current and a valid account is executing the trigger.------------------------------------------------------------------- */ SET @Return = NULL EXECUTE @Return = [dbo].[CheckAccess] @AccountId = @AccountId OUTPUT, @AuditAccessAlias = N'INSERT', @AuditCategoryAlias = N'MACHINE', @AuditCategoryId = @AuditCategoryId OUTPUT, @AuditDatabaseReference = @AuditReferenceUpdate OUTPUT, @CurrentTimestamp = @CurrentTimestamp OUTPUT, @MaximumTimestamp = @MaximumTimestamp OUTPUT, @MessageAlias = @MessageAlias OUTPUT, @Reference = @AuditReferenceInsert SET @Error = @@ERROR IF (@Error <> 0 OR @Return IS NULL OR @Return <> 0) BEGIN SET @MessageAlias = ISNULL(LTRIM(RTRIM(@MessageAlias)),N'TOU59998') SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) RAISERROR (@MessageText,10,1) WITH SETERROR, LOG RETURN END /* ------------------------------------------------------------------- Set default values if not supplied on the INSERTED record. ------------------------------------------------------------------- */ SET @Active = ISNULL(@Active, @CurrentTimestamp) SET @Archive = ISNULL(@Archive, @MaximumTimestamp)/* ------------------------------------------------------------------- Check there is no inconsistant temporal overlap. ------------------------------------------------------------------- */ IF (@Active < @CurrentTimestamp) BEGIN SET @MessageAlias = N'TOU56305' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) RAISERROR (@MessageText,10,1) WITH SETERROR RETURN END IF (@Archive < @Active) BEGIN SET @MessageAlias = N'TOU56306' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) RAISERROR (@MessageText,10,1) WITH SETERROR RETURN END /* ------------------------------------------------------------------- Check the new descriptor is unique. If it isn't unique it will require an UPDATE not an INSERT. ------------------------------------------------------------------- */ BEGIN IF EXISTS ( SELECT * FROM [dbo].[MachineDomain] AS [MachineDomain] INNER JOIN INSERTED AS [Inserted] ON [MachineDomain].[Name] = [Inserted].[Name] ) BEGIN SET @MessageAlias = N'TOU56307' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,N'[Name]') RAISERROR (@MessageText,10,1) WITH SETERROR RETURN END END BEGIN IF EXISTS ( SELECT * FROM [dbo].[MachineDomain] AS [MachineDomain] INNER JOIN INSERTED AS [Inserted] ON [MachineDomain].[Serial] = [Inserted].[Serial] ) BEGIN SET @MessageAlias = N'TOU56307' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,N'[Serial]') RAISERROR (@MessageText,10,1) WITH SETERROR RETURN END END /* ------------------------------------------------------------------- INSERT the new record.------------------------------------------------------------------- */ INSERT [dbo].[MachineDomain] ( [Active], [Archive], [ArchiveInsert], [AuditReferenceInsert], [AuditReferenceUpdate], [Description], [ModelCategoryDomainId], [Name], [Serial] ) SELECT @Active, @Archive, @Archive, @AuditReferenceInsert, @AuditReferenceUpdate, [Description], [ModelCategoryDomainId], [Name], [Serial] FROM INSERTED AS [Inserted] SET @Error = @@ERROR SET @DomainId = SCOPE_IDENTITY() IF (@Error <> 0 OR @DomainId IS NULL OR @DomainId = 0) BEGIN SET @MessageAlias = N'TOU56309' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) RAISERROR (@MessageText,10,1) WITH SETERROR RETURN END UPDATE [dbo].[MachineDomain] SET [Id] = @DomainId WHERE [DomainId] = @DomainId SET @Error = @@ERROR IF (@Error <> 0) BEGIN SET @MessageAlias = N'TOU56310' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) RAISERROR (@MessageText,10,1) WITH SETERROR RETURN END ENDGO [/code] [code="sql"] /* ------------------------------------------------------------------- Trigger MachineDomainInsteadOfUpdate Documentation Project: thisProject Aspect: Core Prepared by: thisOrganisationalUnit For: thatOrganistionalUnit -----------------------------------------------------------------*/ALTER TRIGGER [MachineDomainInsteadOfUpdate] ON [dbo].[MachineDomain] INSTEAD OF UPDATE ASBEGIN SET NOCOUNT ON DECLARE @AccountId [int] DECLARE @AuditAccessAlias [nchar] (24) DECLARE @AuditCategoryId [int] DECLARE @AuditDatabaseReference [uniqueidentifier] DECLARE @Count [int] DECLARE @CountDistinct [int] DECLARE @CurrentTimestamp [datetime] DECLARE @DefaultAuditReferenceUpdate [uniqueidentifier] DECLARE @DeletedActive [datetime] DECLARE @DeletedArchive [datetime] DECLARE @DeletedAuditReferenceInsert [uniqueidentifier] DECLARE @DeletedAuditReferenceUpdate [uniqueidentifier] DECLARE @DeletedDomainId [int] DECLARE @DeletedId [int] DECLARE @Error [int] DECLARE @InsertedActive [datetime] DECLARE @InsertedArchive [datetime] DECLARE @InsertedAuditReferenceInsert [uniqueidentifier] DECLARE @InsertedAuditReferenceUpdate [uniqueidentifier] DECLARE @MaximumTimestamp [datetime] DECLARE @MessageAlias [nchar] (24) DECLARE @MessageObject [nvarchar] (450) DECLARE @MessageText [nvarchar] (400) DECLARE @Return [int] DECLARE @ReturnDomainId [int]/* ------------------------------------------------------------------- Get constants ------------------------------------------------------------------- */ SET @MessageObject = LOWER(N'['+@@SERVERNAME+N'].['+DB_NAME()+N'].['+SESSION_USER+N'].['+OBJECT_NAME(@@PROCID)+N']')/* ------------------------------------------------------------------- UPDATE multiple records in a single batch is not supported, however UPDATE is allowed by specifying the [DomainId] or [Id] of the record. If the [DomainId] is specified on the UPDATE constraint, it must be the currently active record of the [Id] group. If the [Id] is specified on the UPDATE constraint, multiple records may be SELECTed but only the currently active record from the [Id] group will be UPDATEd.------------------------------------------------------------------- */ SELECT @Count = COUNT(*) FROM INSERTED SET @Error = @@ERROR IF (@Error <> 0) BEGIN SET @MessageAlias = N'TOU56301' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) RAISERROR (@MessageText,10,1) WITH SETERROR RETURN END IF (@Count = 0) BEGIN SET @MessageAlias = N'TOU56302' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) RAISERROR (@MessageText,10,1) WITH SETERROR RETURN END IF (@Count > 1) BEGIN SELECT @CountDistinct = COUNT(DISTINCT [Id]) FROM INSERTED SET @Error = @@ERROR IF (@Error <> 0 OR @CountDistinct > 1) BEGIN SET @MessageAlias = N'TOU56303' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) RAISERROR (@MessageText,10,1) WITH SETERROR RETURN END END/* ------------------------------------------------------------------- Get values from the DELETED and INSERTED tables that will be referenced several times in the following checking. As a consequence of allowing UPDATE based on [DomainId] or [Id], [AuditDatabaseStatic].[Reference] has to be included in the SELECT here, rather than waiting for it to be returned from the call to [CheckAccess] as happens in the INSERT triggers and all other procedures, so that the correct record can be retrieved.------------------------------------------------------------------- */ SELECT @DeletedActive = [Deleted].[Active], @DeletedArchive = [Deleted].[Archive], @DeletedAuditReferenceInsert = [Deleted].[AuditReferenceInsert], @DeletedAuditReferenceUpdate = [Deleted].[AuditReferenceUpdate], @DeletedDomainId = [Deleted].[DomainId], @DeletedId = [Deleted].[Id], @InsertedActive = [Inserted].[Active], @InsertedArchive = [Inserted].[Archive], @InsertedAuditReferenceInsert = [Inserted].[AuditReferenceInsert], @InsertedAuditReferenceUpdate = [Inserted].[AuditReferenceUpdate] FROM DELETED AS [Deleted] INNER JOIN INSERTED AS [Inserted] ON [Deleted].[DomainId] = [Inserted].[DomainId] INNER JOIN [dbo].[AuditDatabaseStatic] AS [AuditDatabase] ON [Deleted].[AuditReferenceUpdate] = [AuditDatabase].[Reference] SET @Error = @@ERROR IF (@Error <> 0) BEGIN SET @MessageAlias = N'TOU56311' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) RAISERROR (@MessageText,10,1) WITH SETERROR RETURN END /* ------------------------------------------------------------------- Check the session token is current and a valid account is changing the record. ------------------------------------------------------------------- */ SET @Return = NULL EXECUTE @Return = [dbo].[CheckAccess] @AccountId = @AccountId OUTPUT, @AuditAccessAlias = N'UPDATE', @AuditCategoryAlias = N'MACHINE', @AuditCategoryId = @AuditCategoryId OUTPUT, @AuditDatabaseReference = @AuditDatabaseReference OUTPUT, @CurrentTimestamp = @CurrentTimestamp OUTPUT, @MaximumTimestamp = @MaximumTimestamp OUTPUT, @MessageAlias = @MessageAlias OUTPUT, @Reference = @InsertedAuditReferenceUpdate SET @Error = @@ERROR IF (@Error <> 0 OR @Return IS NULL OR @Return <> 0) BEGIN SET @MessageAlias = ISNULL(LTRIM(RTRIM(@MessageAlias)),N'TOU59998') SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) RAISERROR (@MessageText,10,1) WITH SETERROR, LOG RETURN END /* ------------------------------------------------------------------- Check for the [Active] datetime being set to NULL or copied from the DELETEd table and replace it with @CurrentTimestamp plus the minimum supported milliseconds. ------------------------------------------------------------------- */ IF (@InsertedActive IS NULL OR @InsertedActive = @DeletedActive) SET @InsertedActive = DATEADD(MILLISECOND, +2, @CurrentTimestamp)/* ------------------------------------------------------------------- This is an obscure piece of logic that makes no sense on the surface, it is implemented to allow a record [Archive] to be set to [dbo].[CurrentTimestamp]() to effectively delete the record. The InsteadOfDelete trigger can't be used for a delete because the new [AuditReferenceUpdate] can't be passed on the delete statement, so no audit path can be maintained. To allow for latency between the call to UPDATE and the actual UPDATE occurring affecting the value of [dbo].[CurrentTimestamp](), there is a rule implemented that if INSERTED.[Archive] is DELETED.[Active] plus 2 milliseconds, the request is a DELETE and [Archive] will be set to [dbo].[CurrentTimestamp](). The [AuditReferenceUpdate] is left as the default value so the record can be undeleted again by an update with an [Archive] later than [dbo].[CurrentTimestamp](). ------------------------------------------------------------------- */ IF (DATEADD(MILLISECOND, +2, @DeletedActive) = @InsertedArchive) BEGIN SET @InsertedArchive = DATEADD(MILLISECOND, +2, @InsertedActive) IF (DATEADD(MILLISECOND, -2, @InsertedActive) > @DeletedArchive AND @DeletedAuditReferenceUpdate = @AuditDatabaseReference) BEGIN SET @MessageAlias = N'TOU56312' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) RAISERROR (@MessageText,10,1) WITH SETERROR RETURN END END /* ------------------------------------------------------------------- Check the [Archive] datetime being set to NULL or being copied from the DELETEd table and replace it with @MaximumTimestamp. ------------------------------------------------------------------- */ IF (@InsertedArchive IS NULL OR @InsertedArchive = @DeletedArchive) SET @InsertedArchive = @MaximumTimestamp/* ------------------------------------------------------------------- Check there is no inconsistant temporal overlap. ------------------------------------------------------------------- */ IF (@InsertedActive < @CurrentTimestamp) BEGIN SET @MessageAlias = N'TOU56305' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) RAISERROR (@MessageText,10,1) WITH SETERROR RETURN END IF (@InsertedArchive < @InsertedActive) BEGIN SET @MessageAlias = N'TOU56306' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) RAISERROR (@MessageText,10,1) WITH SETERROR RETURN END IF (@InsertedActive < @DeletedActive) BEGIN SET @MessageAlias = N'TOU56313' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) RAISERROR (@MessageText,10,1) WITH SETERROR RETURN END /* ------------------------------------------------------------------- Check the [Active] and [Archive] dates for discontiguous ranges and don't enforce contiguousness if it isn't there. ------------------------------------------------------------------- */ IF (@DeletedArchive >= @InsertedActive) BEGIN SET @DeletedArchive = DATEADD(MILLISECOND, -2, @InsertedActive) END/* ------------------------------------------------------------------- Check the new descriptor is unique across records other than the currently active [Id] group. ------------------------------------------------------------------- */ BEGIN IF EXISTS ( SELECT * FROM [dbo].[MachineDomain] AS [MachineDomain], INSERTED AS [Inserted] WHERE [MachineDomain].[Id] <> @DeletedId AND [MachineDomain].[Name] = [Inserted].[Name] ) BEGIN SET @MessageAlias = N'TOU56314' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,N'[Name]') RAISERROR (@MessageText,10,1) WITH SETERROR RETURN END END BEGIN IF EXISTS ( SELECT * FROM [dbo].[MachineDomain] AS [MachineDomain], INSERTED AS [Inserted] WHERE [MachineDomain].[Id] <> @DeletedId AND [MachineDomain].[Serial] = [Inserted].[Serial] ) BEGIN SET @MessageAlias = N'TOU56314' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,N'[Serial]') RAISERROR (@MessageText,10,1) WITH SETERROR RETURN END END /* ------------------------------------------------------------------- Updating a record to be a duplicate of itself is not supported except when a DELETE is being executed or a deleted record is being undeleted. ------------------------------------------------------------------- */ IF NOT (@InsertedArchive = DATEADD(MILLISECOND, +2, @InsertedActive)) BEGIN IF EXISTS ( SELECT * FROM DELETED AS [Deleted] INNER JOIN INSERTED AS [Inserted] ON [Deleted].[DomainId] = [Inserted].[DomainId] WHERE [Deleted].[AuditReferenceUpdate] = @AuditDatabaseReference AND [Deleted].[Archive] >= @CurrentTimestamp AND ISNULL([Deleted].[Description], 0) = ISNULL(ISNULL([Inserted].[Description], [Deleted].[Description]), 0) AND [Deleted].[ModelCategoryDomainId] = ISNULL([Inserted].[ModelCategoryDomainId], [Deleted].[ModelCategoryDomainId]) AND [Deleted].[Name] = ISNULL([Inserted].[Name], [Deleted].[Name]) AND ISNULL([Deleted].[Serial], 0) = ISNULL(ISNULL([Inserted].[Serial], [Deleted].[Serial]), 0) ) BEGIN SET @MessageAlias = N'TOU56315' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) RAISERROR (@MessageText,10,1) WITH SETERROR RETURN END END /* ------------------------------------------------------------------- UPDATE the archive timestamp for the existing record then INSERT the new record. ------------------------------------------------------------------- */ UPDATE [dbo].[MachineDomain] SET [Archive] = @DeletedArchive, [AuditReferenceUpdate] = @InsertedAuditReferenceUpdate WHERE [DomainId] = @DeletedDomainId SET @Error = @@ERROR IF (@Error <> 0) BEGIN SET @MessageAlias = N'TOU56316' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,N'[MachineId]') RAISERROR (@MessageText,10,1) WITH SETERROR RETURN END INSERT [dbo].[MachineDomain] ( [Active], [Archive], [ArchiveInsert], [AuditReferenceInsert], [AuditReferenceUpdate], [Description], [Id], [ModelCategoryDomainId], [Name], [Serial] ) SELECT @InsertedActive, @InsertedArchive, @DeletedArchive, @InsertedAuditReferenceUpdate, @AuditDatabaseReference, [Description], @DeletedId, [ModelCategoryDomainId], [Name], [Serial] FROM INSERTED WHERE [DomainId] = @DeletedDomainId SET @Error = @@ERROR SET @ReturnDomainId = SCOPE_IDENTITY() IF (@Error <> 0 OR @ReturnDomainId IS NULL OR @ReturnDomainId = 0) BEGIN SET @MessageAlias = N'TOU56317' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,N'[MachineId]') RAISERROR (@MessageText,10,1) WITH SETERROR RETURN END ENDGO[/code][code="sql"]/* ------------------------------------------------------------------- Procedure SetMachine Documentation Project: thisProject Aspect: Core Prepared by: thisOrganisationalUnit For: thatOrganistionalUnit Description Insert a record into the [Audit] table, then delete, insert or update a record in the [Machine] table. -----------------------------------------------------------------*/CREATE PROCEDURE [SetMachine]( @Active [datetime] = NULL, @Archive [datetime] = NULL, @CategoryId [int] = NULL, -- For compatibility only, use @ModelCategoryId @Description [nvarchar] (900) = NULL, @Id [int] = NULL OUTPUT, @ModelCategoryId [int] = NULL, @Name [nvarchar] (450) = NULL, @Serial [nvarchar] (24) = NULL, @Token [nchar] (36) = NULL)ASBEGIN SET NOCOUNT ON DECLARE @AccountId [int] DECLARE @AuditCategoryId [int] DECLARE @AuditDatabaseReference [uniqueidentifier] DECLARE @CurrentTimestamp [datetime] DECLARE @Error [int] DECLARE @MaximumTimestamp [datetime] DECLARE @MessageAlias [nchar] (24) DECLARE @MessageObject [nvarchar] (450) DECLARE @MessageText [nvarchar] (400) DECLARE @ParameterActive [datetime] DECLARE @ParameterArchive [datetime] DECLARE @ParameterDescription [nvarchar] (900) DECLARE @ParameterId [int] DECLARE @ParameterModelCategoryId [int] DECLARE @ParameterName [nvarchar] (450) DECLARE @ParameterSerial [nvarchar] (24) DECLARE @Reference [uniqueidentifier] DECLARE @Return [int] DECLARE @ThisId [int] DECLARE @Transaction [nchar] (36)/* ------------------------------------------------------------------- Get constants. ------------------------------------------------------------------- */ SET @MessageObject = LOWER(N'['+@@SERVERNAME+N'].['+DB_NAME()+N'].['+SESSION_USER+N'].['+OBJECT_NAME(@@PROCID)+N']') SET @Reference = NEWID() SET @Transaction = CAST(@Reference AS [nchar] (36))/* ------------------------------------------------------------------- Check the session token is current and a valid account is executing the procedure.------------------------------------------------------------------- */ SET @Return = NULL EXECUTE @Return = [dbo].[CheckAccess] @AccountId = @AccountId OUTPUT, @AuditAccessAlias = N'SET', @AuditCategoryAlias = N'MACHINE', @AuditCategoryId = @AuditCategoryId OUTPUT, @AuditDatabaseReference = @AuditDatabaseReference OUTPUT, @CurrentTimestamp = @CurrentTimestamp OUTPUT, @MaximumTimestamp = @MaximumTimestamp OUTPUT, @MessageAlias = @MessageAlias OUTPUT, @Token = @Token SET @Error = @@ERROR IF (@Error <> 0 OR @Return IS NULL OR @Return <> 0) BEGIN SET @MessageAlias = ISNULL(LTRIM(RTRIM(@MessageAlias)),N'TOU59998') SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) GOTO labelERROR END /* ------------------------------------------------------------------- Parse the parameters. ------------------------------------------------------------------- */ SET @ParameterActive = @Active SET @ParameterArchive = ISNULL(@Archive,@MaximumTimestamp) SET @ParameterDescription = CASE LTRIM(RTRIM(@Description)) WHEN N'' THEN NULL ELSE LTRIM(RTRIM(@Description)) END SET @ParameterId = ISNULL(@Id, 0) SET @ParameterModelCategoryId = ISNULL(@ModelCategoryId, 0) IF (ISNULL(@CategoryId,0) <> 0) BEGIN SET @ParameterModelCategoryId = @CategoryId END SET @ParameterName = CASE LTRIM(RTRIM(@Name)) WHEN N'' THEN NULL ELSE LTRIM(RTRIM(@Name)) END/* ------------------------------------------------------------------- Start the transaction------------------------------------------------------------------- */ BEGIN TRANSACTION @Transaction SET @Error = @@ERROR IF (@Error <> 0) BEGIN SET @MessageAlias = N'TOU56526' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) GOTO labelERROR END BEGIN/* ------------------------------------------------------------------- Insert an audit record.------------------------------------------------------------------- */ INSERT [dbo].[AuditTransaction] ( [AuditCategoryId], [Reference], [SessionId] ) SELECT @AuditCategoryId, @Reference, [Id] FROM [dbo].[Session](@CurrentTimestamp) WHERE [Reference] = CAST(@Token AS [uniqueidentifier]) SET @Error = @@ERROR SET @ThisId = @@IDENTITY IF (@Error <> 0 OR @ThisId IS NULL OR @ThisId = 0) BEGIN SET @MessageAlias = N'TOU56538' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) GOTO labelERRORINTRANSACTION END /* ------------------------------------------------------------------- Is the request a DELETE, INSERT or UPDATE------------------------------------------------------------------- */ IF (@ParameterId <> 0 AND @ParameterDescription IS NULL AND @ParameterModelCategoryId = 0 AND @ParameterName IS NULL AND @ParameterSerial IS NULL) GOTO labelDELETE IF (@ParameterId = 0 AND @ParameterModelCategoryId <> 0 AND @ParameterName IS NOT NULL AND @ParameterSerial IS NOT NULL) GOTO labelINSERT IF (@ParameterId <> 0 AND @ParameterSerial IS NULL AND (@ParameterDescription IS NOT NULL OR @ParameterModelCategoryId <> 0 OR @ParameterName IS NOT NULL)) GOTO labelUPDATE BEGIN SET @MessageAlias = N'TOU56061' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) GOTO labelERRORINTRANSACTION END/* ------------------------------------------------------------------- DELETE updates the [Archive] datetime to now which effectively deletes the record while maintaining temporal integrity. If the @Archive parameter has been set to greater than now, it will be used to allow a post-dated delete. The InsteadOfDelete trigger can't be used for a delete because the new [AuditReferenceUpdate] can't be passed on the delete statement, so no audit path can be maintained. So there is a rule implemented that if the InsteadOfUpdate trigger DELETED.[Archive] is INSERTED.[Active] plus 2 milliseconds, the request is a DELETE and [Archive] will be set to now. The [AuditReferenceUpdate] is left as the default value so the record can be undeleted again by an update with an [Archive] later than now. ------------------------------------------------------------------- */ labelDELETE: BEGIN IF (@ParameterArchive IS NULL OR @ParameterArchive = @MaximumTimestamp) BEGIN SET @ParameterArchive = NULL SELECT @ParameterArchive = DATEADD(MILLISECOND, +2, [Active]) FROM [dbo].[MachineDomain] WHERE [Id] = @ParameterId AND [AuditReferenceUpdate] = @AuditDatabaseReference SET @Error = @@ERROR IF (@Error <> 0) BEGIN SET @MessageAlias = N'TOU56539' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) GOTO labelERRORINTRANSACTION END IF (@ParameterArchive IS NULL) BEGIN SET @MessageAlias = N'TOU56063' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) GOTO labelERRORINTRANSACTION END END UPDATE [dbo].[MachineDomain] SET [Archive] = @ParameterArchive, [AuditReferenceUpdate] = @Reference WHERE [Id] = @ParameterId SET @Error = @@ERROR IF (@Error <> 0) BEGIN SET @MessageAlias = N'TOU56540' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) GOTO labelERRORINTRANSACTION END GOTO labelCHANGEDONE END/* ------------------------------------------------------------------- INSERT a new record------------------------------------------------------------------- */ labelINSERT: BEGIN INSERT [dbo].[MachineDomain] ( [Active], [Archive], [AuditReferenceInsert], [Description], [ModelCategoryDomainId], [Name], [Serial] ) VALUES ( @ParameterActive, @ParameterArchive, @Reference, @ParameterDescription, @ParameterModelCategoryId, @ParameterName, @ParameterSerial ) SET @Error = @@ERROR SET @ParameterId = @@IDENTITY IF (@Error <> 0 OR @ParameterId IS NULL OR @ParameterId = 0) BEGIN SET @MessageAlias = N'TOU56541' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) GOTO labelERRORINTRANSACTION END GOTO labelCHANGEDONE END /* ------------------------------------------------------------------- UPDATE an existing record------------------------------------------------------------------- */ labelUPDATE: BEGIN UPDATE [dbo].[MachineDomain] SET [Active] = @ParameterActive, [Archive] = @ParameterArchive, [AuditReferenceUpdate] = @Reference, [Description] = CASE WHEN @ParameterDescription IS NOT NULL THEN @ParameterDescription ELSE [Description] END, [ModelCategoryDomainId] = CASE WHEN @ParameterModelCategoryId <> 0 THEN @ParameterModelCategoryId ELSE [ModelCategoryDomainId] END, [Name] = CASE WHEN @ParameterName IS NOT NULL THEN @ParameterName ELSE [Name] END WHERE [Id] = @ParameterId SET @Error = @@ERROR IF @Error <> 0 BEGIN SET @MessageAlias = N'TOU56542' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) GOTO labelERRORINTRANSACTION END GOTO labelCHANGEDONE END/* ------------------------------------------------------------------- END transaction, COMMIT the changes------------------------------------------------------------------- */ END labelCHANGEDONE: BEGIN COMMIT TRANSACTION @Transaction SET @Error = @@ERROR IF @Error <> 0 BEGIN SET @MessageAlias = N'TOU56527' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) GOTO labelERRORINTRANSACTION END SET @Id = @ParameterId RETURN 0 END /* ------------------------------------------------------------------- Error termination routines. ------------------------------------------------------------------- */ labelERRORINTRANSACTION: BEGIN ROLLBACK TRANSACTION @Transaction GOTO labelERROR END labelERROR: BEGIN SET @Id = NULL RAISERROR (@MessageText,10,1) RETURN CAST(RIGHT(LTRIM(RTRIM(@MessageAlias)),5) AS [int]) END ENDGO[/code][code="sql"]/* ------------------------------------------------------------------- Procedure GetMachine Documentation Project: thisProject Aspect: Core Prepared by: thisOrganisationalUnit For: thatOrganistionalUnit Description Get a resultset from the [Machine] table. @Serial is an alias for the [Id] and is a unique serial number from the manufacturer. -----------------------------------------------------------------*/CREATE PROCEDURE [GetMachine]( @Active [datetime] = NULL, @AuditId [int] = NULL, @Id [int] = NULL OUTPUT, @Name [nvarchar] (450) = NULL, @Serial [nvarchar] (24) = NULL, @Token [nchar] (36) = NULL)ASBEGIN SET NOCOUNT ON DECLARE @AccountId [int] DECLARE @Error [int] DECLARE @Location [nvarchar] (450) DECLARE @MessageAlias [nchar] (24) DECLARE @MessageObject [nvarchar] (450) DECLARE @MessageText [nvarchar] (400) DECLARE @ParameterActive [datetime] DECLARE @ParameterAuditId [int] DECLARE @ParameterId [int] DECLARE @ParameterName [nvarchar] (450) DECLARE @ParameterSerial [nvarchar] (24) DECLARE @Return [int] DECLARE @RowCount [int]/* ------------------------------------------------------------------- Get constants.------------------------------------------------------------------- */ SET @MessageObject = LOWER(N'['+@@SERVERNAME+N'].['+DB_NAME()+N'].['+SESSION_USER+N'].['+OBJECT_NAME(@@PROCID)+N']')/* ------------------------------------------------------------------- Parse the parameters------------------------------------------------------------------- */ SET @ParameterActive = @Active SET @ParameterAuditId = @AuditId SET @ParameterId = ISNULL(@Id, 0) SET @ParameterName = CASE LTRIM(RTRIM(@Name)) WHEN N'' THEN NULL ELSE LTRIM(RTRIM(@Name)) END SET @ParameterSerial = CASE LTRIM(RTRIM(@Serial)) WHEN N'' THEN NULL ELSE LTRIM(RTRIM(@Serial)) END/* ------------------------------------------------------------------- Check the combination of parameters------------------------------------------------------------------- */ IF (@ParameterId <> 0 AND @ParameterName IS NULL AND @ParameterSerial IS NULL) GOTO labelOK IF (@ParameterId = 0 AND @ParameterName IS NOT NULL AND @ParameterSerial IS NULL) GOTO labelOK IF (@ParameterId = 0 AND @ParameterName IS NULL AND @ParameterSerial IS NOT NULL) GOTO labelOK IF (@ParameterId = 0 AND @ParameterName IS NULL AND @ParameterSerial IS NULL) GOTO labelOK BEGIN SET @MessageAlias = N'TOU56061' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) GOTO labelERRORMESSAGESPECIFIED END /* ------------------------------------------------------------------- Check the session token is current and a valid account is reading the record. ------------------------------------------------------------------- */ labelOK: SET @Return = NULL EXECUTE @Return = [dbo].[CheckAccess] @AccountId = @AccountId OUTPUT, @Active = @ParameterActive OUTPUT, @AuditAccessAlias = N'GET', @AuditCategoryAlias = N'MACHINE', @AuditId = @ParameterAuditId, @MessageAlias = @MessageAlias OUTPUT, @Token = @Token SET @Error = @@ERROR IF (@Error <> 0 OR @Return IS NULL OR @Return <> 0) BEGIN SET @MessageAlias = ISNULL(LTRIM(RTRIM(@MessageAlias)),N'TOU59998') SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) GOTO labelERRORMESSAGESPECIFIED END /* ------------------------------------------------------------------- Get the resultset.------------------------------------------------------------------- */ BEGIN SET NOCOUNT OFF SELECT [Description], [Id], [ModelCategoryId], [Name], [Serial] FROM [dbo].[Machine](@ParameterActive) WHERE CASE @ParameterId WHEN 0 THEN 0 ELSE [Id] END = @ParameterId AND CASE ISNULL(@ParameterName,N'') WHEN N'' THEN N'' ELSE [Name] END = ISNULL(@ParameterName,N'') AND CASE ISNULL(@ParameterSerial,N'') WHEN N'' THEN N'' ELSE [Serial] END = ISNULL(@ParameterSerial,N'') SELECT @Error = @@ERROR, @RowCount = @@ROWCOUNT SET NOCOUNT ON IF (@Error <> 0) BEGIN SET @MessageAlias = N'TOU56521' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) GOTO labelERRORMESSAGESPECIFIED END IF (@RowCount = 0) BEGIN SET @MessageAlias = N'TOU56063' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,default) GOTO labelERRORMESSAGESPECIFIED END RETURN 0 END/* ------------------------------------------------------------------- Generic error handling------------------------------------------------------------------- */ labelERROR: BEGIN SET @MessageAlias = N'TOU59999' SET @MessageText = [dbo].[AuditMessageFormat](@MessageAlias,@MessageObject,@Location) GOTO labelERRORMESSAGESPECIFIED END labelERRORMESSAGESPECIFIED: BEGIN RAISERROR (@MessageText,10,1) RETURN CAST(RIGHT(LTRIM(RTRIM(@MessageAlias)),5) AS [int]) END END GO[/code]Fri, 02 Oct 2009 20:38:49 GMTwldhrshttp://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspx[quote]I have to mention that seeing the title of this article made me wonder if SSC had changed into a different type of website overnight.[/quote]I agree with that. I thought I was going to witness the "softer" side of technology. Effective Dating could be important considering the long hours many of us have to work. I can date, but, doing it effectively takes additional skills. I was especially interested in the date recovery plan or the date backup plan. Can I restore the date back to the point before I answered the question, "Do these pants make me look fat?" :hehe:Fri, 02 Oct 2009 15:29:37 GMTOCTomhttp://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspxThis was an excellently written article. It makes a point to both technical and non-technical people. I have read the replies and have to say, as with cats there are many ways to skin them. I would not use the design as presented, yet I understand the presenter was trying to get a specific point across about temporal database design and not how to effective design database entity keying relationhsips.We have “Lunch-N-Learns” at my organization and I am going to use this article as the basis of one of those sessions… with required attendance (we provide the lunch when attendance is required). Everyone in a data-centric organization should be aware of these principles, whether they are techies or not.Thank you Sam and I look forward to your next article.Fri, 02 Oct 2009 08:28:00 GMTScott Rankinhttp://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspx[quote][b]Richard Gardner-291039 (10/2/2009)[/b][hr]Quite - as the DBA you should be revoking all privileges on all tables and only allowing pre designed updates via stored procedures - that's the only way you can guarantee data integrity. Are your developers DBAs? No. So why give them the opportunity to compromise your data? Yeah, yeah, rapid response yadda yadda, flexibility yadda yadda, all means nothing when your data is compromised.[/quote]No, the only way you can get close to guaranteeing data integrity is to implement database constraints that, when active, actively prevent data which lacks integrity from being written to the database.Further, there is a huge difference in most actual businesses between whatever that businesses's security ideal is, and the security reality there. There is also a large degree of variation on what the security ideal is. Regardless of the desired state, the actual state in many places is different; even if you do and can locking SQL writing access down to DBA's doesn't guarantee that a DBA won't make a mistake at some point. All DBA's are humans, no humans are perfect, therefore, no DBA's are perfect.A function based CHECK constraint to verify that date ranges that don't overlap is philosophically not significantly different than choosing to use NUMERIC(9) instead of CHAR(9) for 9 digit numbers, or to have a CHECK constraint limiting an indicator field to only the four possible valid values. Yes, it's more expensive, but all methods I've seen are expensive; I prefer the data integrity method that is:A) Most likely to functional at any given point in time (I see triggers disabled more often than constraints, and I see stored procedures bypassed more often than I see triggers disabled)B) More easily checked in a generic fashion (DBCC CHECKCONSTRAINTS)C) Capable of preventing issues in the widest range of data write methods (write methods include but are not limited to stored procedures, triggers, ad-hoc SQL from applications, hand-written ad-hoc SQL, bcp/BULK INSERT, and so on).D) Closest to "guaranteeing" that the bad data does not currently exist, even if it existed prior to the most recent enabling/updating of the prevention mechanism.I've often seen instances of bad data, and when I ask someone else, the answer is "Oh! That shouldn't happen." Occasionally, the answer is "That can't happen," which is clearly false as it did happen. Database level constraints are the best way I know of to limit issues from happening; whether they're function based and expensive, whether they're an IN ('M','F','U') check, or whether they're choosing NUMERIC(9) or another datatype instead of CHAR(9).Fri, 02 Oct 2009 06:25:18 GMTNadrekhttp://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspxQuite - as the DBA you should be revoking all privileges on all tables and only allowing pre designed updates via stored procedures - that's the only way you can guarantee data integrity. Are your developers DBAs? No. So why give them the opportunity to compromise your data? Yeah, yeah, rapid response yadda yadda, flexibility yadda yadda, all means nothing when your data is compromised.Fri, 02 Oct 2009 04:37:09 GMTRichard Gardner-291039http://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspx[quote][b]Jason Whitney (10/1/2009)[/b][hr][quote][b]jcrawf02 (10/1/2009)[/b][hr][quote][b]Nadrek (10/1/2009)[/b][hr][quote][b]jcrawf02 (10/1/2009)[/b][hr]Why would you want the db to do this? Why wouldn't you require the application to..[clipped]..the app takes care of the rest. [/quote]I'm a DBA, so yes, the db must be perfect. :-D Also we may have multiple applications accessing a single database so I don't want to rely on the application logic.[/quote]That's what stored procedures are for.Fri, 02 Oct 2009 03:52:04 GMTrobertmhttp://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspxThis will make my job obsolete as a BI person but ... Add an identity column (surrogate key), and a Change_Type (price change, promo etc) to the renamed Prod_Hist (was called Promo_Prod) along with the natural keys (only really need the from date as these must be continuous from and to dates - more than one promo active does not make sense to me - sounds like a larger thing than product - we call it a bundle in telco - thats another discussion).Use the TSQL Merge Statement as follows ...For new (inserted) products you create a new Promo_Prod with no to date or largest date possible for the data type (means no expiry) and the current price. For changes to product price use merge to update old price's to date and insert new price from date with same to date and null for the to date. Same for promos changes. When orders are created they use the price in the Prod-Hist since it only exists there. There's a lot more to this for complex requirements but this is a good taste for a simple requirement around price and promo changes.excuse my quick answer for spelling, clarity etc.Thu, 01 Oct 2009 17:48:17 GMTmartinko_michaelhttp://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspx[quote][b]James Stephens (10/1/2009)[/b][hr]As a developer, the best practice here in my opinion, is to wrap all data update/inserts/deletes for those table into a stored procedure with the validation logic. All apps would have no ability to update that data without calling the stored procedure to proxy the work. I know this may not help for existing legacy apps where source code isn't available and recompiling (if it's a compiled app) isn't possible, but the more business rules that can go on the server and not on the client, the better we are.--Jim[/quote]That's a great approach to strive for, but unless insert, update, and delete are revoked on the table, somebody might still connect outside of your application and enter data directly into it that violates business rules. Also, this approach does not eliminate any overhead, it just moves it to a different place.On the bright side, if the rules are enforced from insert/update triggers, the entire table does not need to cross join to itself, it just needs to cross join with the "inserted" temp table that is provided to the trigger by the database engine. Hopefully only a small number of rows (if more than one) will ever be inserted/updated at a time during normal production activity.Thu, 01 Oct 2009 15:18:56 GMTAndy DBAhttp://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspxAs a developer, the best practice here in my opinion, is to wrap all data update/inserts/deletes for those table into a stored procedure with the validation logic. All apps would have no ability to update that data without calling the stored procedure to proxy the work. I know this may not help for existing legacy apps where source code isn't available and recompiling (if it's a compiled app) isn't possible, but the more business rules that can go on the server and not on the client, the better we are.--JimThu, 01 Oct 2009 14:59:08 GMTJames Stephenshttp://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspx[quote][b]jcrawf02 (10/1/2009)[/b][hr][quote][b]Nadrek (10/1/2009)[/b][hr][quote][b]jcrawf02 (10/1/2009)[/b][hr]Why would you want the db to do this? Why wouldn't you require the application to..[clipped]..the app takes care of the rest. [/quote]Philosophically, with this argument, why do you want the DB to have _any_ constraints at all?Because I assume there are multiple apps, at least one is flawed, and at least some rows will sooner or later be entered another way.[/quote]Hardly saying that, but you're arguing that the db needs to be perfect where the app is not, versus the reverse. Since the goal of the app should be to reduce the work the user has to perform to accomplish the task, it should already be doing this.[/quote]I'm a DBA, so yes, the db must be perfect. :-D Also we may have multiple applications accessing a single database so I don't want to rely on the application logic. Also, the application should reduce the work a user has to perform, but this is completely different from accurately modeling the data. Part of data modeling in this case is the ability to store interval or ranged data (dates or numbers) that do not overlap.Thu, 01 Oct 2009 14:41:20 GMTJason Whitneyhttp://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspx[quote][b]Nadrek (10/1/2009)[/b][hr][quote][b]jcrawf02 (10/1/2009)[/b][hr]Why would you want the db to do this? Why wouldn't you require the application to..[clipped]..the app takes care of the rest. [/quote]Philosophically, with this argument, why do you want the DB to have _any_ constraints at all?Because I assume there are multiple apps, at least one is flawed, and at least some rows will sooner or later be entered another way.[/quote]Hardly saying that, but you're arguing that the db needs to be perfect where the app is not, versus the reverse. Since the goal of the app should be to reduce the work the user has to perform to accomplish the task, it should already be doing this.Thu, 01 Oct 2009 14:27:55 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspx[quote][b]jcrawf02 (10/1/2009)[/b][hr]Why would you want the db to do this? Why wouldn't you require the application to..[clipped]..the app takes care of the rest. [/quote]Philosophically, with this argument, why do you want the DB to have _any_ constraints at all?Because I assume there are multiple apps, at least one is flawed, and at least some rows will sooner or later be entered another way.Thu, 01 Oct 2009 14:19:41 GMTNadrekhttp://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspxWhy would you want the db to do this? Why wouldn't you require the application to term the existing record with the timestamp when the change occurred -1 (millisecond, second, whatever) and start a new record with the timestamp when the change occurred to whatever the termdate is? Then the user just hits a button or whatever and the app takes care of the rest. Thu, 01 Oct 2009 14:04:48 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspx[quote][b]Jason Whitney (10/1/2009)[/b][hr]I have run into this before, and the article gives the first half of the solution. The second part is what I can't figure out; how to avoid overlapping time-frames.The supplied solution doesn't enforce data integrity for overlapping dates. For the illustration given it may not need to, but for many applications these date ranges must be mutually exclusive. For example a patient comes in to the hospital as an outpatient (O) and has a complication and gets admitted as an inpatient (I). The data is temporal, but the dates/times cannot overlap. How would I model this?The only solution I have been able to come up with is a new data type for interval data. (Hopefully we will see this in the next version of SQL) I tried a CLR, but I was never able to get it to work quite right. My database has failed me! :crazy:[/quote]Yes, that's what I was talking about, and that is the serious problem with trying to represent an interval within a row in SQL Server.You can do a function based check constraint that applies one of the available overlap detections, such as the simple (and SQL Server 2000 friendly). As mentioned, it's expensive. There may be better solutions for 2000, and I think there are for 2005, but I don't know them.Essentially:CROSS JOIN the table to itself (call them Outer and Inner)WHERE Inner.UniqueRowKey <> Outer.UniqueRowKey (one row doesn't overlap with itself)AND Inner.KeyThatShouldNotOverlap = Outer.KeyThatShouldNotOverlap (so we only look at row sets that, in combination, can overlap when they shouldn't)AND Inner.effdate <= Outer.termdate (one starts before/equal to when the other ends)AND Inner.termdate >= Outer.effdate (and that one also ends after/equal to when the other starts)Add in as many fields in composite keys as you need.If you don't have a truly unique row identifier... add one to the table, build one, concatenate fields to get one, or do some AND/OR within nested parenthesis as appropriate.Thu, 01 Oct 2009 11:58:20 GMTNadrekhttp://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspxI have run into this before, and the article gives the first half of the solution. The second part is what I can't figure out; how to avoid overlapping time-frames.The supplied solution doesn't enforce data integrity for overlapping dates. For the illustration given it may not need to, but for many applications these date ranges must be mutually exclusive. For example a patient comes in to the hospital as an outpatient (O) and has a complication and gets admitted as an inpatient (I). The data is temporal, but the dates/times cannot overlap. How would I model this?The only solution I have been able to come up with is a new data type for interval data. (Hopefully we will see this in the next version of SQL) I tried a CLR, but I was never able to get it to work quite right. My database has failed me! :crazy:Thu, 01 Oct 2009 11:46:11 GMTJason Whitneyhttp://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspxWow 'em with your disaster recovery model! :-PThu, 01 Oct 2009 11:01:14 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspxYes...I'm glad I'm not the only one who thought this was a "How to pick-up chicks" article!Thu, 01 Oct 2009 10:39:03 GMTJames Stephenshttp://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspx[quote][b]GSquared (10/1/2009)[/b][hr]I have to mention that seeing the title of this article made me wonder if SSC had changed into a different type of website overnight. ;-)[/quote]Heh, so did I. I have to admit though that like many developers, I could maybe use some pointers there as well...Thu, 01 Oct 2009 10:35:34 GMTweitzerahttp://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspxI use Ceridian HR/Payroll which uses this scheme. While I agree fully with the concept, there is a huge performance hit.Have you ever seen the Execution plan when you try to run any query that uses Temporal Dating? In a nutshell, plan on nested full-table scans, especially in the case you have multiple related tables with effective dating. You're putting "OR" clauses on not single fields, but two fields based on a range of dates.Plus, as was mentioned before, there's no primative, database-level way to prevent overlap.This is where a datawarehouse, in a flat-schema (not star) model comes in handy. In payroll, each day we have a transaction--person A worked X hours. His pay scale on that day was X. Once that day is done, that 'X' payscale is set in stone--it can be loaded to a transaction-detail table with the payscale embedded in the table--not joined to the 'payscale' table with effective dating, and all reporting is ultra fast and accurate. This is true for the numerous key fields Ceridian uses that are joined from a dozen or more 'effective dated' tables.Just my thoughts...--JimThu, 01 Oct 2009 10:14:18 GMTJames Stephenshttp://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspx[quote][b]Richard Gardner-291039 (10/1/2009)[/b][hr]You're talking about a different data set, that information is in your sales ledger.[/quote]You have a point, however, if I'm trying later on to provide data to marketing with regards to discounts and their effects on sales, the resulting price per unit on the ledger may not provide me with a reliable enough number. Having the ability to get the exact PPU during that promotion would be, in my opinion, better served by having a way to recreate the exact price at that time without having to go to the ledger, the end price of which may be affected by other factors such as good customer discounts, volume discounts, etc (as you mentioned). Most of that discussion is admittedly academic without discussing the actual schema and business rules for that particular "what if". :)[quote][b]Richard Gardner-291039 (10/1/2009)[/b]I'd say it adds a lot to the flexibility, you can then treat the discount rate as a "what if" scenario - granted you'd have to create a view onto the data which works out the actual selling price after discount, then join that view to your reports (not so difficult), but think about sales forecasting and margin calculations (remember margin is a product of COGS, NOT current selling price, which to mind mind is irrelevant in this context), you'd have hard and fast historical data about what sort of discount generates what kind of extra demand pretty simply available - then you could start forecasting with different discount rates and tailor the promotions accordingly.You can do this with the existing model, granted, but it makes it much more difficult. Also the existing model doesn't lend itself well to fast setup, personally I'd be looking into categorising products (if only by margin) so they can be treated en-masse in "promotional groups"[/quote]All excellent points. Again, I think the best approach on this one would likely be situational.Thu, 01 Oct 2009 09:47:11 GMTGarry Morrishttp://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspxawww! I thought this article was on soft skills... badly needed in the DBA community. :-DThu, 01 Oct 2009 09:43:48 GMTSQL_EXPAThttp://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspx[quote][b]Nadrek (10/1/2009)[/b][hr]...With most Start date + End date designs, you can trivially end up with multiple simultaneously overlapping date ranges, and thus, in your example... which promotion is really in effect? Are all of them in effect? Does one or more override others?I.e. if we usually end up with (in your example) promotions on the same products that have date ranges of 8/1 to 8/15, 8/2 to 8/30, 8/10 to 8/18, 8/11 to 12/31....[/quote]Business rules might allow simultaneous promotions. I've rented cars online before and had to enter a promotion code for discounted rates (eg. Senior Citizen or Emerald Aisle). The person/process entering the order has to be aware of the promotion, of course. The "promotion code" (promotion PK) can be placed in the OrderHeader or OrderDetail table depending on whether or not business rules allow multiple promotions in a single order. Also, if regular prices are included as "promotions", a left join (and associated overhead) is not required to retrieve product pricing and historical reporting (eg. reprinting an order form) does not require date comparisons. This also treats non-sale price changes in a temporal fashion and supports pre-entry of price change information.Thu, 01 Oct 2009 09:41:09 GMTAndy DBAhttp://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspxNice article - looking forward to see how it plays out.Thu, 01 Oct 2009 09:05:46 GMTGlen Cooperhttp://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspx[quote][b]jcrawf02 (10/1/2009)[/b][hr]Not sure I completely follow what Nadrek is saying, but had a thought. (who knew?)How in a global enterprise would you account for sales beginning/ending across time zones? Multiple records for each? Or adjust based on local time in the application?[/quote]Been there, not going there again. The short answer is VERY TEDIOUSLY.Thu, 01 Oct 2009 08:39:55 GMTRichard Gardner-291039http://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspxJust do it in your business logic - when you want to add a product to a promotion ensure the application has to call a stored procedure - in that procedure first check to see whether the product is already in a promotion. If it is then check the dates don't overlap. If they do then throw an error.I can't see any inherent advantage in building this into the database structure, unless you're so chaotic you're allowing developers to throw data into your database at random.Thu, 01 Oct 2009 08:31:06 GMTRichard Gardner-291039http://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspxNot sure I completely follow what Nadrek is saying, but had a thought. (who knew?)How in a global enterprise would you account for sales beginning/ending across time zones? Multiple records for each? Or adjust based on local time in the application?Thu, 01 Oct 2009 08:29:54 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspxUnfortunately, with SQL and temporal databases, you appear to have missed one of the most difficult aspects of generalized date based fields: how to be both efficient and prevent bad data from getting in.With most Start date + End date designs, you can trivially end up with multiple simultaneously overlapping date ranges, and thus, in your example... which promotion is really in effect? Are all of them in effect? Does one or more override others?I.e. if we usually end up with (in your example) promotions on the same products that have date ranges of 8/1 to 8/15, 8/2 to 8/30, 8/10 to 8/18, 8/11 to 12/31.Simple unique indexes/constraints do not prevent this. More complex function based check constraints can, but are expensive. Some other techniques also exist, but are again expensive. Storing only one date (start date) and then looking for the closest greater start date as the "day after the end date" is also expensive (and then you may need to guarantee such a record exists).Note: for SQL Server 2005 and less, if you intend only midnight based dates, further check constraints are required to enforce a midnight time portion of the datetime/smalldatetime fields, otherwise all applications need to do work to zero out times (non-midnight times on "date" (small)datetime fields tend to accrue over time).Thu, 01 Oct 2009 08:24:52 GMTNadrekhttp://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspx[quote]As to the person wondering about discount rates, there are pros and cons to that. The pro, as mentioned, is not maintaining a separate list of hard prices. The immediately obvious con is figuring out later, say 6-mos down the road, when products prices' may have changed, just how much that product cost under that promotion.[/quote]You're talking about a different data set, that information is in your sales ledger.I'd say it adds a lot to the flexibility, you can then treat the discount rate as a "what if" scenario - granted you'd have to create a view onto the data which works out the actual selling price after discount, then join that view to your reports (not so difficult), but think about sales forecasting and margin calculations (remember margin is a product of COGS, NOT current selling price, which to mind mind is irrelevant in this context), you'd have hard and fast historical data about what sort of discount generates what kind of extra demand pretty simply available - then you could start forecasting with different discount rates and tailor the promotions accordingly.You can do this with the existing model, granted, but it makes it much more difficult. Also the existing model doesn't lend itself well to fast setup, personally I'd be looking into categorising products (if only by margin) so they can be treated en-masse in "promotional groups"Thu, 01 Oct 2009 07:57:49 GMTRichard Gardner-291039http://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspx[quote][b]ddunn-1115368 (10/1/2009)[/b][hr]Great aticle! I've been trying to get this concept across to the developers of an accounting package that I work with. Managing changes in price lists (never mind promotional items) can be a bear. This process would simplify so many things for the end users. Being able to import "next week's price list" would be a great feature.I would also consider using the date associated with the document header to locate these promo items instead of a system date. There will be cases where you have to back date a transaction and using the document's date vs. the system date will allow that.[/quote]I used the Placed (datetime) column of the Orders table to join to the pricing data in effect at the time the order was placed. That way, if you have to print out the order form a year later, you don't get current pricing, you get the price the order actually dealt with, which is important.I also had a nullable "ContractID" column in the pricing tables that allowed customers to have specific contracts that gave them their own pricing structure, since that was important to the business. That data was also stored in the Orders table.Thu, 01 Oct 2009 07:51:37 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspx[quote][b]dan-579597 (10/1/2009)[/b][hr]Thinking about where to put the dates: I'm not sure if this is better or just over-engineering, but what about a PromotionInstance (PI) table between Promotion (P) and PromotionProduct (PP) with the start and end dates in PI. Either give PI its own ID, e.g. PromotionInstanceID, that can be part of the PP PK (instead of the PromotionID), or have the Start and End dates be part of the PI PK and have the PP table look as it does in the article. The advantages I see are small, but here they are: - You can have a recurring promotion described in P ("Semi-Annual Sale"), then have the specific occurrence's details in PI ("Fall 2009 Back To School Sale") and the products are strongly coupled to a real-life event.- A better level of normal form by having the PP dates either as FK to PI or by eliminating them from PP in favor of a PI ID. This makes me more comfortable in with data integrity, though I can see the argument that it's safe enough if the dates in PP are system-managed.Promotions that only ever occur once would just have one instance, of course.[/quote]Would you ever need to treat the multiple occurrences of a sale like your 'semi-annual sale' as one object? If not, I'd think this is overkill, as each occurrence would be treated and analyzed as a separate animal.Thu, 01 Oct 2009 07:41:25 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspxThinking about where to put the dates: I'm not sure if this is better or just over-engineering, but what about a PromotionInstance (PI) table between Promotion (P) and PromotionProduct (PP) with the start and end dates in PI. Either give PI its own ID, e.g. PromotionInstanceID, that can be part of the PP PK (instead of the PromotionID), or have the Start and End dates be part of the PI PK and have the PP table look as it does in the article. The advantages I see are small, but here they are: - You can have a recurring promotion described in P ("Semi-Annual Sale"), then have the specific occurrence's details in PI ("Fall 2009 Back To School Sale") and the products are strongly coupled to a real-life event.- A better level of normal form by having the PP dates either as FK to PI or by eliminating them from PP in favor of a PI ID. This makes me more comfortable in with data integrity, though I can see the argument that it's safe enough if the dates in PP are system-managed.Promotions that only ever occur once would just have one instance, of course.Thu, 01 Oct 2009 07:36:51 GMTlosreedhttp://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspx[quote][b]jcrawf02 (10/1/2009)[/b][hr]Mod -1, how is this article supposed to help me get a date?![/quote]Read the title - "Part 1 - The Problem". It's a demonstration - you read the article and commented on an SQL forum. Case in point. Hopefully next week there'll be a cure for this kind of behaviour, or at least how to effectively hide it from the opposite sex.:-PThu, 01 Oct 2009 07:14:10 GMTRichard Gardner-291039http://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspxMod -1, how is this article supposed to help me get a date?!Thu, 01 Oct 2009 07:04:38 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspxYou said "heinous":hehe:Thu, 01 Oct 2009 06:57:18 GMTscott mcnitthttp://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspxAgreed with RobertM, there is a place for dates in both tables depending on the business strategy. Imagine if you had the same promotion with a rolling list of products to be discounted (e.g. the "Two for One" promotion that lasts all summer long, but which features a new/different product every week).As to the person wondering about discount rates, there are pros and cons to that. The pro, as mentioned, is not maintaining a separate list of hard prices. The immediately obvious con is figuring out later, say 6-mos down the road, when products prices' may have changed, just how much that product cost under that promotion.Thu, 01 Oct 2009 06:56:33 GMTGarry Morrishttp://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspxGreat aticle! I've been trying to get this concept across to the developers of an accounting package that I work with. Managing changes in price lists (never mind promotional items) can be a bear. This process would simplify so many things for the end users. Being able to import "next week's price list" would be a great feature.I would also consider using the date associated with the document header to locate these promo items instead of a system date. There will be cases where you have to back date a transaction and using the document's date vs. the system date will allow that.Thu, 01 Oct 2009 06:53:24 GMTddunn-1115368http://www.sqlservercentral.com/Forums/Topic796136-1607-1.aspxWhen I've done this before, I've kept the dates in the promotions table. I called it a "PricingScheme" table, since it included other things that just special promotions, but it's the same concept.I have to mention that seeing the title of this article made me wonder if SSC had changed into a different type of website overnight. ;-)Thu, 01 Oct 2009 06:44:29 GMTGSquared