On Thu, 5 May 2005, Simon Perreault wrote:
In fact, your solution is a way to move the set logic from the app to the SQL server. I don't think it's a good idea. You might as well have the SQL server call a procedure using DateTime::Sets directly, it would be the same computation. Nothing is gained, except that the logic is stored unserialized, and IMHO it's more appropriate to have the application server, not the DB server, do the application logic.
The set _is_ data! The fact that it requires some involved procedural logic doesn't change the fact that the at its core, it is data, and as such properly belongs in the DBMS. Unfortunately, there is no DBMS that i know with good support for this sort of thing as a first class data type. But generally speaking, storing sets as scalar data in the DBMS is totally legit. The fact that these sets can be infinite certainly makes things harder, but it doesn't mean that it shouldn't be done.
Well, not really. Suppose I want to store N sets. That would create N views (not counting the sub-views). Now that the sets are stores, I want to know "which sets intersect with today?" That's equivalent to "which views contain a row that intersects with today?" I don't think that's possible to know without iterating on the view names.
Yeah, this is definitely a smell. I think the smell here is that the storage mechanism for a single piece of scalar data is being blown up into multiple tables. What you really want is a native data type with the appropriate operators, so you can do:
SELECT ... FROM RecurringEvent WHERE INTERSECTS(SET, $datetime)
That'd be sweet.
I like to think my method is pragmatic. I deal with finite and infinite sets differently. I store the recursion rule as an ICal string in a column and in another column I use a bool to indicate whether the set is finite or infinite. If it's finite, I expand the recursion to all its occurrences in a table of occurrences. Easy. If it's infinite, then there is simply nothing else to do. It's up to the application to select all infinite sets, parse the rule and do the calculation. As others mentioned, I cache occurrences of infinite recursions in a separate table, so the calculation needs not be done every time.
Yeah, this has been discussed a bit earlier in the thread. This is probably the sanest way to do without support for these sets as a first class data type.
I do wonder how hard it would be to at least implement a _finite_ set as a first class data type. In Postgres, Perl is one of the languages you can use for server side programming. I haven't tried implementing a data type in Perl, but now I'm quite curious.
It seems like for many applications, you could get away with never using an infinite recurrence. There are a couple options:
- Tell the user up front that they can only store X (say, 25) years worth, then make it easy to extend a soon-to-expire recurrence.
- Bound the set at some ridiculously far future date (y3k) and store that bounded set.
Either of these would be acceptable for things like a group web calendar, but if you're looking at something on an astronomic scale (storing something like rotations of the galaxy ;) it might be a problem.
-dave
/*=================================================== VegGuide.Org www.BookIRead.com Your guide to all that's veg. My book blog ===================================================*/
