Re: Best way to effective date??

In article <2p1t4e$pp0_at_uuneo.neosoft.com>

           chadb_at_NeoSoft.com "Chad A. Brockman" writes:

>
> We are having a major debate over the best way to do effective dating. If we
> just use <entity><effective date><effective dated info> it seems that the sql
> to get at these dates is really wierd. It would have to involve major sorting
> (unless the field was indexed which can pretty much be assumed). So to get
> the effective data for a date would require something like:
>
> SELECT max(effective_date)
> FROM mytable
> where entity_id = :entity_id_var and
> effective_date <= :date_to_get_effective_data_for;
>
> This seems awfull ineffecient. Anybody got any better ideas???
>

If I read your entities correctly, this type of statement is in fact very efficient. Assuming you have a table MYTABLE which stores information with an effective date (eg. emp, dname, effective_date - stores the date an employee is assigned to a department), you can find the current department for each employee using:
  select emp, dname
    from mytable m
   where m.effective_date =

         (select max(m1.effective_date)
            from mytable m1
           where m1.emp = m.emp
             and m1.dname = m.dname
             and m1.effective_date >= sysdate)
             

This assumes your transactions are not 'end dated' - IMHO the best design decision in appropriate circumstances, however if you do go with an end date, you just add an extra condition checking the end date against the reference date (sysdate used here, but in practice it will often be a supplied variable). You can use between if your reference date is not null.

I agree it looks terribly inefficient and perhaps even a bit scary at first glance, but try it out and see for yourself. While I cannot give you any guarantees about performance in your application, I have used this type of data structure for some time, and seen it perform in a lot of heavy duty production environments. Not only is it fast, it does not depend on the use of a (sometimes) redundant end date to find a 'current' record.

-- 
Glenn Nicholas, Concept
Glenn_at_wplace.demon.co.uk
Opinons expressed are my own.