Maintaining two different versions of a record in SQL Server -

i have sql server table has following columns:

id, headertext, contenttext, proposedcontentid, active 

i want maintain 2 versions of records edited users using website. example, user john edit headertext , contenttext fields of following record , click save.

    id,    headertext,    contenttext,    proposedcontentid, active ==> 1,    hello,          test,    null,             1 

but instead of updating same record want create record , point old record new record. table looks this:

     id, headertext,    contenttext,         proposedcontentid, active      1, hello,          test,           2,              0  ==> 2, hello world,    new post,      null,            1 

as can see, old record marked not active , proposedcontentid points newly created row.

i don't know whether solution problem. please suggest better ways if there any.

edit: need maintain 2 copies (old , new) , cannot create tables.

i not use active flag way of tracking latest record. type of identifier gets tricky maintain.

i add content identifier table. then, when record gets changed, insert new data same content identifier. id field auto increment, or add datetime field, can track active record looking @ record highest id (or latest timestamp) given contentid.

id,   contentid   headertext       contenttext           1     1           hello            test         2     1           hello world      new post     3     1           hello again!     content changed again 4     2           new content      new text   5     2           newer content    other record, updated 

you add in active flag setup if want, doesn't have more complex each record having it's own identifier contentid , knowing active record whatever has highest id, or latest timestamp. you'd prefer it.

if wanted "active" records, you'd need run this:

select a.* yourtable join ( select contentid, max(id) maxid yourtable group contentid) b on a.contentid = b.contentid , a.id = b.maxid 

that should give following:

id,   contentid   headertext       contenttext           3     1           hello again!     content changed again 5     2           newer content    other record, updated 

i hope helps or @ least gives food thought.