I have a lookup table that implements a system-generated Id for DRI purposes. The lookup table has columns defining the effective and obsolete dates of the record i.e. the period during which a lookup term is valid for business use. Normally I would create a unique index on the lookup term to ensure that it is not duplicated. However, the business wishes to reserve the right to be able to reuse a previously used, now obsolete, term - because there are only so many good words to go around they may want to reuse a previously obsoleted term but give it a slightly different meaning (If they wish to start using the same term with the same meaning again they'd just remove the obsolete date). In this scenario old records, using the obsolete term, would still need to reference the now-obsolete version while newly created records would reference the new instance of the term
I thought I could simply modify my unique index on the lookup term to include the obsolete date but this doesn't produce the desired result. Note that I have the same requirement for the Business_Code column.
Is there a straight-forward way of doing what my customer is asking?
Here's my lookup table DDL. This will eventually be turned into a SP that I would use to generate all reference tables with the same spec.. The "Reference_Table" term will be replaced by a variable that I will pass to the SP.
DROP TABLE IF EXISTS [dbo].[R_Reference_Table]
CREATE TABLE [dbo].[R_Reference_Table] (
[Reference_Table_Id] INT NOT NULL IDENTITY(1, 1) CONSTRAINT [PKC_Reference_Table] PRIMARY KEY CLUSTERED
,[Reference_Table_Name] VARCHAR(255) NOT NULL
,[Reference_Table_Description] VARCHAR(MAX) NULL
,[Reference_Table_Business_Code] VARCHAR(12) NULL
,[Reference_Table_Effective_Date] DATE NOT NULL CONSTRAINT [DF_Reference_Table_Effective_Date] DEFAULT GETDATE()
,[Reference_Table_Obsolete_Date] DATE NULL
,[Reference_Table_Created_On] DATETIME NOT NULL CONSTRAINT [DF_Reference_Table_Created_On] DEFAULT GETDATE()
,[Reference_Table_Updated_On] DATETIME NOT NULL CONSTRAINT [DF_Reference_Table_Updated_On] DEFAULT GETDATE()
,[Reference_Table_Updated_By] VARCHAR(51) NOT NULL CONSTRAINT [DF_Reference_Table_Updated_By] DEFAULT [dbo].[Get_User_Name]()
,[Reference_Table_RowVersion] ROWVERSION NOT NULL
,[Reference_Table_GUID] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_Reference_Table_GUID] DEFAULT NEWID()
,CONSTRAINT [CK_Obsolete_Date_GE_Effective_Date] CHECK ([Reference_Table_Obsolete_Date] >= [Reference_Table_Effective_Date])
,CONSTRAINT [UIX_Unique_Effective_Reference_Table_Name] UNIQUE ([Reference_Table_Name], [Reference_Table_Obsolete_Date])
,CONSTRAINT [UIX_Unique_Effective_Reference_Table_Business_Code] UNIQUE ([Reference_Table_Business_Code], [Reference_Table_Obsolete_Date])
CREATE TRIGGER [dbo].[R_Reference_Table_On_Update] ON [dbo].[R_Reference_Table] FOR UPDATE AS
SET NOCOUNT ON
UPDATE TBL SET
TBL.[Reference_Table_Updated_On] = GETDATE()
,TBL.[Reference_Table_Updated_By] = [dbo].[Get_User_Name]()
FROM [dbo].[R_Reference_Table] TBL
JOIN inserted INS
ON TBL.[Reference_Table_Id] = INS.[Reference_Table_Id]
Thanks in advance for any advice you can provide.
- This topic was modified 4 weeks, 1 day ago by CanuckBuck.