October 1, 2012 at 11:10 am
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.
October 1, 2012 at 1:27 pm
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
October 1, 2012 at 2:54 pm
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.
October 1, 2012 at 7:11 pm
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/
My thought question: Have you ever been told that your query runs too fast?
My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 2, 2012 at 9:10 am
Interesting dwain.c, thanks for the link.
Mr. Celko inspired my design choice based on previous post so I have come full circle.
October 3, 2012 at 7:04 am
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
October 3, 2012 at 7:30 am
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.
My thought question: Have you ever been told that your query runs too fast?
My advice:
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?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
October 3, 2012 at 7:41 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply