If I use the following table design to capture history...:
CREATE TABLE MyTable ( insertion_timestamp TIMESTAMP, deleted_flag BOOLEAN, natural_key INT, attribute VARCHAR );
... then what should the primary key be?
(The history mechanism is
INSERT-only: updated rows are inserted with a different
insertion_timestamp, and deleted rows are inserted with a different timestamp and the
deleted_flag set to
PRIMARY KEY (insertion_timestamp, deleted_flag, natural_key), but the only reason for including
deleted_flag is to account for the possibility of a row being inserted and then (soft) deleted immediately, sooner than the next tick of the granularity of the
TIMESTAMP data type. This feels overly paranoid...