Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Effective Dating Series Part I - The Problem


Effective Dating Series Part I - The Problem

Author
Message
Nadrek
Nadrek
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1029 Visits: 2673
Jason Whitney (10/1/2009)
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


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.
jcrawf02
jcrawf02
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1420 Visits: 19324
Why 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.

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Nadrek
Nadrek
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1029 Visits: 2673
jcrawf02 (10/1/2009)
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.


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.
jcrawf02
jcrawf02
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1420 Visits: 19324
Nadrek (10/1/2009)
jcrawf02 (10/1/2009)
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.


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.

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.

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Jason Whitney
Jason Whitney
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 553
jcrawf02 (10/1/2009)
Nadrek (10/1/2009)
jcrawf02 (10/1/2009)
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.


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.

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.


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.
James Stephens
James Stephens
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 210
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
Andy DBA
Andy DBA
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 Visits: 753
James Stephens (10/1/2009)
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


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.



martinko_michael
martinko_michael
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 134
This 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.
robertm
robertm
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 118
Jason Whitney (10/1/2009)
jcrawf02 (10/1/2009)
[quote]Nadrek (10/1/2009)
[quote]jcrawf02 (10/1/2009)
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.


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.


That's what stored procedures are for.



Richard Gardner-291039
Richard Gardner-291039
SSC Veteran
SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)

Group: General Forum Members
Points: 231 Visits: 421
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search