locked
A POINT IN TIME Architecture Implemented in SQL 2005+ RRS feed

  • Question

  • Hello Gurus!

    I am a challenged with a problem to design a database for a client that tracks history, no records are physically destroyed and we need to go back in time to generate reports on history tables, such as when a particular teacher got promoted, what designations were held by him/her or at various points of time. I have gone through the article of Mr. Arthur Fuller at http://www.simple-talk.com/sql/database-administration/database-design-a-point-in-time-architecture/. I like his idea overall but I don't like changing the primary key again and again and I would love to store data into a separate history table. Any techniques, tools or sample are highly appreciated.

     

    Monday, March 29, 2010 5:44 AM

Answers

  • Create new set of tables with same structure and one new addition column to store date and this column will be part of primary key. daily data can be moved to this history table. for better performance create partition on date column
    Monday, March 29, 2010 6:14 AM
  • Sen,

    One quick bits on this updating the data in order to take the exam again would be, you can frame some algorithm that help you to catch and avoid duplicates like changing name from Rahul to Rahul Srivatsa etc.

    THink in this direction to add respective parameters to track a unique student at the time of adding/updating.


    Regards, KarthikShanth. "Mark as Answer" if this helps you!
    Monday, March 29, 2010 10:03 AM

All replies

  • Create new set of tables with same structure and one new addition column to store date and this column will be part of primary key. daily data can be moved to this history table. for better performance create partition on date column
    Monday, March 29, 2010 6:14 AM
  • Some kind of Trail tables will also help you to do features of Audits where you will do a immediate inserts into the trails whenever there is a change in your main table using triggers.
    Regards, KarthikShanth. "Mark as Answer" if this helps you!
    Monday, March 29, 2010 7:08 AM
  • Thanks for the reply! 

    I have already considered that, but if I need to go back to time and generate reports based on history tables? How can I join for I have multiple primary keys in the History tables. Further I have scenario where we hire an employee on a contract basis for one year and then the same employee is hired again after next two years. How to maintain this?

    Monday, March 29, 2010 9:39 AM
  • Thanks!

    Our requirement is history tracking i.e. returning to a point in time. For example a student who named RAHUL enrolls in class 8th and subsequently goes on to class 12. He gets failed in class X and repeats the exam, changes his name to "Rahul Srivastva" and changes his medium of study in class X. Now imagine the whole scenario for a school and then possibly for an entire state. Basically, the system needs to track students. We need to generate reports like how many student in the particular year failed, how many took the repeat exam. We need to go back in past to find such details. How many teacher were teaching mathematics and total available science teacher at that point of time.

     

    Thanks!

    Monday, March 29, 2010 9:47 AM
  • Oh Yes!

    Your idea of creating partition on Date column sounds good.

    Thanks!

    Monday, March 29, 2010 9:56 AM
  • Sen,

    One quick bits on this updating the data in order to take the exam again would be, you can frame some algorithm that help you to catch and avoid duplicates like changing name from Rahul to Rahul Srivatsa etc.

    THink in this direction to add respective parameters to track a unique student at the time of adding/updating.


    Regards, KarthikShanth. "Mark as Answer" if this helps you!
    Monday, March 29, 2010 10:03 AM