Re: Checking data consistency in the context of from-to-dimensions
Date: Tue, 30 Jun 2009 02:17:50 -0700 (PDT)
This book is available at the site of university of Arizona for
Other books also cover temporal data in some chapters, such as Joe
Celko's SQL for Smarties.
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
- 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