Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from stackoverflow.com Jul 5 '12 at 14:33

This question came from our site for professional and enthusiast programmers.

    
I dislike suffixing (or prefixing) column names with their datatypes. I think I prefer insertedAt, isActive (!deleted_flag). –  Clockwork-Muse Jul 2 '12 at 20:54
    
Fair points both. –  jl6 Jul 2 '12 at 21:08

2 Answers 2

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.
share|improve this answer
    
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 Jul 2 '12 at 21:07

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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