Steve Jones: I agree with you, and I think I’m going to start from the beginning and do a series on this. I guess I wanted to know if there was interest out there for this topic…and I think that question has been answered :-)
smunson: I’m not sure what you mean by ‘re-use’, but I think you mean that you want to reuse an old row of data? You can do this, but then you won’t have any history of past promotions and when they applied. And if that’s OK for your implementation, then you don’ t need Effective Dating at all. The point I was trying to make is: how do you structure a table when you need to have historical, current, and future rows in it? That’s the scenario I’m dealing with. Once you have to deal with these requirements, then the complications start to appear…
larry.smith: Yes, you have part of the idea that I’m trying to convey…but the missing part is: why should we need to write all this trigger or constraint logic by hand? I believe that a new datatype is in order that handles these functions seamlessly so we don’t have to. That’s the rationale behind the INTERVAL datatype.
randy: What you mentioned is another option for dealing with Effective Dating…using a StartDate and Duration instead of a StartDate and an EndDate. I went with the 2 dates because I thought it would be simpler to do a BETWEEN instead of having to calculate the EndDate all the time. Although you could do this with a computed column to simplify things, it would take up more CPU. I guess I prefer storing the dates because the most common queries in my scenario call for a BETWEEN and I have little use for the Duration…although I could calculate that if necessary as well.
Manie Verster: Sure, you can run reports on the table that has the 2 dates and things appear to work fine, but how would you prevent 2 projects from overlapping? And how would you handle Referential Integrity from other tables? How about gaps in the dates…would you allow or disallow them? There are a whole host of issues that appear when you try to incorporate Effective Dating, and this was the point of the article. Many of these problems have to be solved by programming, and my argument is that with an INTERVAL datatype this could be built into the Database Engine and make things much simpler.
As to your comment about the time, it depends on the granularity of time in the requirements. If you only need to keep track of DATES without a TIME, then you don’t need the time in there as it will only sow confusion. But there are scenarios where you may want rows to appear or disappear in the middle of the day, and you would need the TIME component for that.
marco: I’m not sure I agree with the Database sticking to primitive types. After all, what is a ‘primitive’ type? Years ago you could have said that the DATETIME datatype was very complex and should not be handled in the database either. Yet here it is and it’s been a resounding success. Recently, SQL 2005 and 2008 have come out with several new datatypes based on the CLR integration that store more complex information and they have created some very elegant solutions with these new datatypes. Check out the Spatial and Hierarchy datatypes in SQL 2008, for example. So DB vendors are constantly pushing back the limits of what is ‘too complex’ to be represented as a datatype. The question that remains is: will this happen for date/time intervals? Maybe we’re not at the point yet where we know enough about it to standardize it and encapsulate it into a datatype. But maybe we are….the INTERVAL datatype would be complex, and there would probably be many attributes that you would be able to set to ‘customize’ it for your purposes, but in the end we are dealing with date periods, which are a very common component of everyday life, so they should be encapsulated in a datatype if possible.
Thanks for the feedback!
No comments:
Post a Comment