Re: Checking data consistency in the context of from-to-dimensions

From: Philipp Post <>
Date: Tue, 30 Jun 2009 02:17:50 -0700 (PDT)
Message-ID: <>

>Snodgrass has an earlier book length treatment of the topic in SQL.<

This book is available at the site of university of Arizona for download

Other books also cover temporal data in some chapters, such as Joe Celko's SQL for Smarties.

The general patter I know off is having <something>_start_date and <something>_end_date. NULL in the end_date means current. That makes it easy to get the current data, for what you could build a view.

Regarding data integrity the following comes to mind (certainly not a complete list):
- CHECK(start_date < end_date)
- regarding overlaps you could say, that the new start_date or
end_date is not allowed to be between the existing start_date and end_date pairs applicable.
- just one NULL in the end_date by article to be allowed. COUNT can be helpfull there. COUNT(end_date) gives you the count of all NOT NULL entries, COUNT(*) applied to all columns, includes those with NULL in the end_date
- triggers, views with check option or assertions (most products do not have that) could be used to enforce your rules.


Philipp Post Received on Tue Jun 30 2009 - 11:17:50 CEST

Original text of this message