sql - RDBMS primary key design for row versioning -

i want design primary key table row versioning. table contains 2 main fields : id , timestamp, , bunch of other fields. unique "id" , want store previous versions of record. hence creating primary key table combination of id , timestamp fields. hence see versions of particular id, can give,

select * table_name id=<id_value> 

to return recent version of id, can use

select * table_name id=<id_value> order timestamp desc 

and first element. question here is, query efficient , run in o(1) instead of scanning entire table entries matching same id considering id field part of primary key fields? ideally result in o(1), should have provided entire primary key. if need entire table scan, how else can design primary key request done in o(1)?

thanks, sriram

the canonical reference on subject effective timestamping in databases: https://www.cs.arizona.edu/~rts/pubs/vldbj99.pdf

i design subset of paper's recommendations, using table containing primary key only, referencing table has key change_user, valid_from , valid_until colums appropriate defaults. makes referential integrity easy, future value insertion , history retention. index appropriate, , consider check constraints or triggers prevent overlaps , gaps if expose these fields application direct modification. these have obvious performance overhead.

we make "current values view" exposed developers, , insertable via "instead of" trigger.