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

> NULL anywhere is a really dumb idea. Why not check out the theoretical work that has already been done instead of making uninformed observations? <

Basically I fully agree with your comments on NULL. However due to the fact that SQL supports CURRENT_DATE / CURRENT_TIMESTAMP in queries but not stored in a table, which would constantly change, you would most likely end up using a date long in the future to represent current valid data. But which one would you pick? 9999-12-31 or 3000-01-01 or 2999-31-12 or whatever else? Further this is no logically correct representation of "now" resp. "today" either. Therefore I consider it a valid approach to use in this specific case NULL for the current entries which do not have an end_date specified yet and build a VIEW on top of it using COALESCE(end_date, CURRENT_DATE) in order to always have the right values in it.

> But that doesn't even begin to deal with the declarative constraints for the normal requirements of full coverage, adjacency and non-overlap. <

Fair enough. Mea culpa.

brgds

Philipp Post Received on Wed Jul 01 2009 - 14:39:01 CEST