Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009
    Posts
    8

    How to design changing data fields?

    Friends,

    Please help me to undestand the best design to tackle this recurring problem.
    I have a EMPLOYEE_MASTER table with the following structure. EMP_DESIGNATION may change over time. I want to keep the history of such changes for future references. What is the most efficient approach to store designation here? Should I move it to another table with an effective start date and end date?

    EMPLOYEE_MASTER(EMPID,EMPNAME,EMP_DESIGNATION)

    Thanks,
    Harish.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by haris77
    Should I move it to another table with an effective start date and end date?
    Spot on.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Run this code with your table name as the parameter,
    and it will generate a script for creating an archive table, along with the triggers necessary to maintain it. It will record every change to the data in your table.
    sqlblindman private pastebin - collaborative debugging tool
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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