7

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 true.)

I'm thinking 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...

2
  • 1
    I dislike suffixing (or prefixing) column names with their datatypes. I think I prefer insertedAt, isActive (!deleted_flag). Commented Jul 2, 2012 at 20:54
  • Fair points both.
    – jl6
    Commented Jul 2, 2012 at 21:08

2 Answers 2

3

I'm thinking 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...

I don't know if it's too paranoid, but it's reasonable to just stipulate that no two rows may share the same insertion_timestamp and natural_key, and make it the responsibility of the clients doing the inserts to handle the edge case. It simplifies using the table considerably if you can assume that those combinations are unique—plus in the tripartite key you suggested, it's tricky to order the rows in the triply-paranoid case where there are two immediate "soft" deletes in a row.

So my recommendation would be PRIMARY KEY (natural_key, insertion_timestamp) (in that order—but I'm guessing natural_key is going to be queried for more often, which could be wrong).

Also, you should seriously consider the following additions:

  1. Add an inactive_timestamp column to the tables. This allows you to query for rows that were active at a point in time.
  2. Think carefully about what the timestamps mean. Is the table supposed to represent the history of the real-world values ("Joe's phone number was 555-5555 until January 22, then it was 666-6666 from January 22"), or history of modifications to an "eternal" value? Your schema looks like it's going after the latter, but make sure you understand which is going to be needed.
1
  • Thanks for your answer. 1. That would make the query simpler and probably faster but I'm just trying to fully normalize the model at the moment. 2. I'm aware of bitemporal methods; my example is the simplest case. 3. Could you elaborate on the problem of row ordering that you refer to in the triply-paranoid case?
    – jl6
    Commented Jul 2, 2012 at 21:07
3

In many applications, it is common to use a db-automatically generated number like a sequence number as PK. If the table does not have a naturally key and you don't want to use an artificial key (as I suggested), and the table is not a parent table, you don't have to create a PK at all (you can still create non-unique indexes on any combination of columns you want).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.