Enforcing effective dated records in sql

Question

Suppose I have a record with a column called "Name". Each record can be effective dated, so I also have an effective date and an expiry date column.

The "Name" column has to be unique, but only within overlapping periods of the effective date and expiry date. I'm looking for some sort of efficient way to enforce this from within the sql back end (I am using ms sql 2008).

Answer 1

You would use an INSERT/UPDATE trigger for this.

Example C on that page is similar to what you need. Inserted is a pseudo table that you can use to join your inserted/updated records back to your table to see if there are any other records with overlapping dates,

Answer 2

Here is the data model

Table A (

name char (32) not null,
effdt date not null,
effseq int not null,
effstatus char (1) not null,

other data columns ..

)

Primary_Key A_Key (name, effdt, effseq);

Here is the data:

'A', '1/1/2009', 1, 'A', 'Otherdata'
'A', '1/1/2009', 2, 'A', 'Otherdata 2'
'B', '1/1/2009', 1, 'A', 'B data'
'B', '1/1/2009', 2, 'I', 'B Data'

Here are the rules:

Use Effdt to store the date on which the row should be used or effective. This may not be the date on which the data is stored/created.

Use Effseq to store multiple updates on any date. It should always start with 1.

Use EFFStatus to inactivate the data. Do not ever delete data in an effective dated schema. Deleting also makes the business of Auditing hard.

When you "Update" a row, never update the EFFDT column. Always create a new row with new EFFDT. If EFFDT already exists, use the next EFFSEQ.

It may be a good idea to store the updateuserid and updatetimestamp also in the row. Especially if you want to keep track of the changes.

Here are the queries:

To get all data:

Select * from A

To get all "Active Rows" as of today:

Select * from A A1
where effdt = (select max (EFFDT) from A where name= A1.name 
                                      and effdt <= getdate())
and effseq = (select max(effseq) from A where name=A1.name and effdt=A1.effdt)
and eff_status = 'A'

To get all rows as of a given date past/future:

Replace the getdate() with the actual date in the above.

If you are writing a web app, here are the form field rules:

Insert form:

EFFDT : Editable, Default Value = Today
EFFSEQ : Non Editable, Default Value = "#Next". 
Interpret what is "Next" in the backend.

Update form:

EFFDT : Editable, Default= current Value.
EFFSEQ : Non-Editable, Default = "#Next"

Delete form:

All data non editable, On Delete Action, set EFF_STATUS='I'

History Edit:

This is a "super user" feature only. 
EFFDT = Non Editable, Default is Current Date
EFFSEQ = Non-Editable, Default is Current Value

All other fields editable, and do a real Update on the row.