2

Lets say for an example we have a table called student (sID is the primary key, isValid=1 means the row is valid and 0 is invalid for the moment)

sId    sName   gpa   isValid 
1      Tom      3.0    1
2      Hanks    3.5    1

now let's assume that I have updated Tom's values. but still I want to save the previous values.

sId    sName   gpa   isValid 
1      Tom      3.0    0
2      Hanks    3.5    1 
1      Tom      3.6    1

Now the Toms values has changes. nut his previous values haven't permanently deleted.

how can I implement this is there a plugin or something?. The problem is that primary key is occurring more than once.

NULL_USER
  • 125
  • 1
  • 2
  • 8

2 Answers2

1

You need to change database design.

Keep in sId,sName and any other field(if required) in student table which will be parent table. Create another table like student_academic with sId as foreign key of student table along with gpa, isvalid fields.

For each student values changes insert new transcation entry. This also ensures database Normalization and will fulfill your need.

Hardik Mishra
  • 14,779
  • 9
  • 61
  • 96
  • I'm with this solution. In parent table you put the ID and the non changing values. In the linked table you can put the changing values and just indicate with a flag which one is active (the isValid column you mentioned). No need for a specific library here, plain SQL. – hcpl Apr 12 '12 at 06:35
  • That's not the solution required.. you are saying that in your answer student table becomes a static table? What if I change student name and want to track that change also?.. Can't I solve this using the "student" table only?... because I think I won't be able to change the structure of the database. – NULL_USER Apr 12 '12 at 07:01
  • @Ashan : You haven't mentioned about that. Then in that case you need to add an extra id field which will be NULL for first entry. When you change the data, add new entry but also add previous "sId" in that id field. – Hardik Mishra Apr 12 '12 at 07:13
  • @Mishra Sorry about that mate. then how can I track the changes for a single record. Since there will be more than one row for a particular sID. I can use a sequence-Id field to track the change sequence for a particular record. – NULL_USER Apr 12 '12 at 07:26
0

I dont feel the need for any plugin here, I would have done something like this:

  1. Make your sID autoincrement
  2. For every insert, get the last version (sId - 1) and mark it as isValid = 0

I assume you are using jdbc

mprabhat
  • 20,107
  • 7
  • 46
  • 63
  • No the primary key value can't be change. Since I want to track all the changes for that particular record along with the time. – NULL_USER Apr 12 '12 at 06:24
  • definition of primary key is to be unique so it should definitely change, you need another column like record_id(this will be same across all version maintaining your version) – mprabhat Apr 12 '12 at 06:48