Uros Trebec wrote: > > > To summarize, in the past I've used a time_from/time_thru pair of > > date/time columns to make it more efficient to retrieve the version of > > a row as it looked at a particular point in time. Your design of just > > using change_date makes this more difficult. > > I don't know what you mean exactly, but I'm not using just > change_date. The ID in *_history table defines the "revision/version > number", so you don't have to use "change_date" to get the exact > revision.
Let me clarify. What I meant was that your design makes it hard to directly query the row that was in effect at a certain point of time, i.e. given a date/time, how do I find the record that was current at that instant in time? In your model I would have to use a query like this to find the active record for 1/1/06: select * from FooHist where change_date = (select max(change_date) from FooHist where change_date < '2006-01-01') So you find the most recent change that occurred *before* the date in question, which requires a subselect. That is a bit ugly, inefficient, and I think very difficult to map to the Django DB API. With a time_from/time_thru model such a query looks like this: select * from FooHist where time_from <= '2006-01-06' and (time_thru > '2006-01-06' or time_thru is null) So here we are looking for the row who's *active interval* contains the date in question which is a simple, direct query (no subselect). The test for null is a special case for the version of the row that is current (has no end date). I've seen other people use a sentinal value like '9999-12-31' to make the query a little simpler (but then you get that magic date all over the place). I know some people might say this smells of premature optimization, but in my experience - where I have had to make a lot of applications work correctly for a past date - you may end joining many tables with such an expression and the subselects will kill you. You are simply adding one more date/time field to allow joining the table via time more easily. Since this is a *history* table, joining based on time is a very common use case. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Django developers" group. To post to this group, send email to django-developers@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/django-developers -~----------~----~----~----~------~----~------~--~---
