Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Enforcing data integrity, preventing...
Add to briefcase

Enforcing data integrity, preventing over-lapping date ranges Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Chrissy321
Posted Monday, October 01, 2012 11:10 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:01 PM
Points: 550, Visits: 2,978
Hello All,

I typically have join tables where I maintain the current and historical relationships between different entities here represented by Code1 and Code2.

Please note this is not my actual table design, just a test table meant to illustrate the problem.

CREATE TABLE Test
(
[Code1] [varchar](1) NOT NULL,
[Code2] [varchar](1) NOT NULL,
[StartDate] [date] NOT NULL,
[EndDate] [date] NULL
)

A NULL EndDate would indicate the current relationship. A record with both StartDate and EndDate would indicate a historical relationship.

Is it possible in the name of data integrity to alter the table design in such a way to dis-allow over lapping ranges?

Thanks if you can help.
Post #1366671
GSquared
Posted Monday, October 01, 2012 1:27 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569
The way to do that is to avoid Start and End columns. Just have Start, or just End. Then set a Unique index on the code and the Start date, and you're good to go.

It's easy enough to query the table joined to itself to determine if there's a record with a later Start date. If not, it's the current value. If so, it's historical.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1366737
Chrissy321
Posted Monday, October 01, 2012 2:54 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:01 PM
Points: 550, Visits: 2,978
That would certainly simplify table design and integrity enforcement with a slight increase in querying complexity. I'll look at my data model and evaluate the cost of changing this.

I still may stick with the existing data model. One of my goals with this project is absolute ease of data querying although as GSSquared says the self join is fairly simple and I could encapsulate this in a view.

If I do stick with the model a trigger or a complicated check constraint would be acceptable to me since my data sets are currently small (several hundreds and unlikely to go over 1000) and infrequently updated (once a week maybe).

Thanks for you input.
Post #1366778
dwain.c
Posted Monday, October 01, 2012 7:11 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:01 PM
Points: 2,340, Visits: 3,163
You may want to take a look at this article, by our own, beloved Joe Celko: http://www.simple-talk.com/sql/t-sql-programming/contiguous-time-periods/

No loops! No CURSORs! No RBAR! Hoo-uh!

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1366846
Chrissy321
Posted Tuesday, October 02, 2012 9:10 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:01 PM
Points: 550, Visits: 2,978
Interesting dwain.c, thanks for the link.

Mr. Celko inspired my design choice based on previous post so I have come full circle.
Post #1367110
GSquared
Posted Wednesday, October 03, 2012 7:04 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569
dwain.c (10/1/2012)
You may want to take a look at this article, by our own, beloved Joe Celko: http://www.simple-talk.com/sql/t-sql-programming/contiguous-time-periods/


The solution Joe provides doesn't actually prevent gaps. The PreviousEndDate column joins it to a previous row and denormalizes the data into the current row, but it doesn't actually force the start date to have zero gap from the previous end date. If you want to enforce a lack of gaps, you could make the start date FK to another row's end date, but then you don't need the PreviousEndDate column.

So it either allows gaps, or is 100% redundant. One or both.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1367646
dwain.c
Posted Wednesday, October 03, 2012 7:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:01 PM
Points: 2,340, Visits: 3,163
GSquared (10/3/2012)
dwain.c (10/1/2012)
You may want to take a look at this article, by our own, beloved Joe Celko: http://www.simple-talk.com/sql/t-sql-programming/contiguous-time-periods/


The solution Joe provides doesn't actually prevent gaps. The PreviousEndDate column joins it to a previous row and denormalizes the data into the current row, but it doesn't actually force the start date to have zero gap from the previous end date. If you want to enforce a lack of gaps, you could make the start date FK to another row's end date, but then you don't need the PreviousEndDate column.

So it either allows gaps, or is 100% redundant. One or both.


Interesting take on that. I haven't actually used the approach myself. Just looked clever so I bookmarked it for future consideration.

No loops! No CURSORs! No RBAR! Hoo-uh!

INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!
Post #1367671
GSquared
Posted Wednesday, October 03, 2012 7:41 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, May 06, 2013 1:09 PM
Points: 15,439, Visits: 9,569
GSquared (10/3/2012)
dwain.c (10/1/2012)
You may want to take a look at this article, by our own, beloved Joe Celko: http://www.simple-talk.com/sql/t-sql-programming/contiguous-time-periods/


The solution Joe provides doesn't actually prevent gaps. The PreviousEndDate column joins it to a previous row and denormalizes the data into the current row, but it doesn't actually force the start date to have zero gap from the previous end date. If you want to enforce a lack of gaps, you could make the start date FK to another row's end date, but then you don't need the PreviousEndDate column.

So it either allows gaps, or is 100% redundant. One or both.


On further review, it also doesn't prevent overlaps.

And it has technical issues (untrusted constraint, for one). Some are mentioned in the discussion on Simple-Talk, others become obvious if you play with the code for a few minutes.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1367679
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.