Design for time sensitive data

[Ricardo]

I'm looking for the best way to design tables for time sensitive data. Effective
dating seems to be standard approach but the costs associated with retrieving
for large tables seems too high. If this is the best way than how do you
implement the data base for optimum performance (indexing schemes...).

Example of what I think effective dating means...

Table1
Data1
Data2
StartDate
EndDate

Select * from table1 where (startdate <= date and (enddate >= date or enddate
is null))

[Dennis]

"Ricardo" <rkeller99@hotmail.com> wrote:
>
>I'm looking for the best way to design tables for time sensitive data.
Effective
>dating seems to be standard approach but the costs associated with retrieving
>for large tables seems too high. If this is the best way than how do you
>implement the data base for optimum performance (indexing schemes...).
>
>Example of what I think effective dating means...
>
>Table1
> Data1
> Data2
> StartDate
> EndDate
>
>Select * from table1 where (startdate <= date and (enddate >= date or enddate
>is null))

If you're dealing with historical data and don't need the historical data
in the same table as "current" data--and your database supports triggers,
one solution is to create a second, historical table. When a row is updated
in the "current" table, its Update trigger adds a historical row to the history
table. Your latest information is always in the primary table and queries
are fast. Views of history run on the second table.

Dennis

[Ian M. Johnson]

"Ricardo" <rkeller99@hotmail.com> wrote:
>
>I'm looking for the best way to design tables for time sensitive data.
Effective
>dating seems to be standard approach but the costs associated with retrieving
>for large tables seems too high. If this is the best way than how do you
>implement the data base for optimum performance (indexing schemes...).
>
>Example of what I think effective dating means...
>
>Table1
> Data1
> Data2
> StartDate
> EndDate
>
>Select * from table1 where (startdate <= date and (enddate >= date or enddate
>is null))

[Ian M. Johnson]

"Ricardo" <rkeller99@hotmail.com> wrote:
>
>I'm looking for the best way to design tables for time sensitive data.
Effective
>dating seems to be standard approach but the costs associated with retrieving
>for large tables seems too high. If this is the best way than how do you
>implement the data base for optimum performance (indexing schemes...).
>
>Example of what I think effective dating means...
>
>Table1
> Data1
> Data2
> StartDate
> EndDate
>
>Select * from table1 where (startdate <= date and (enddate >= date or enddate
>is null))

I've had lots of luck with the Datediff function. You can even suppliment
it by picking
out sat & sun for turnarounds in weekdays.
Using this function you count the number of days, weeks, quarters... between
2 dates.
The function returns a number which you can easily use for turnaround time
calculations.