DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Design for time sensitive data

  1. #1
    Ricardo Guest

    Design for time sensitive data


    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))

  2. #2
    Dennis Guest

    Re: Design for time sensitive data


    "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

  3. #3
    Ian M. Johnson Guest

    Re: Design for time sensitive data


    "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))



  4. #4
    Ian M. Johnson Guest

    Re: Design for time sensitive data


    "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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center