Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2007
    Posts
    24

    Patterns for things that change with time

    Hi
    i have an table where i store addresses of my contact. A contact may change address and i want to keep track of these changes in order to be able,for example, to determine what address a contact had on a certain data. Is there some pattern describing this kind of temporal issue?
    thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm not sure what a pattern is, but one common way of doing what you ask is to have two additional datetime columns in the table, effective_from and effective_to

    effective_to would be set to NULL for the current address

    when the contact changes, set effective_to to the change datetime, and insert a new row with the change datetime as effective_from and NULL as effective_to

    to find the current address, just use
    Code:
    ... WHERE effective_to is null
    to find the address at any time in the past, use
    Code:
    ... WHERE '2002-11-15' between effective_from and effective_to
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2007
    Posts
    24
    thank you very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •