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

>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

http://www.cs.arizona.edu/~rts/tdbbook.pdf

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.

brgds

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