CODEWITHSUNDEEP
sqlsql-serverdatabase-design
Three Value Logic
Three Value Logic

Reputation: 1112

Database Design: Stored Record Edit History (Temporal Data)

I want to store temporal information in a database. I have come up with the design below. Is this the best way to do it?

MasterTable

DetailsTable

While it works for my purposes having a MasterTable with just an ID field just does not feel right however I can see no other way to link the Details records together.

Is there a cleaner / standard way to do this?

Upvotes: 3

Views: 256

Answers (2)

bjnr
bjnr

Reputation: 3437

An idea would be to design 2 tables as follows:

Entity table: EntityId - PK, Title, Content

EntityHistory table: EntityId - PK, Version - PK, CreatedOn, Title, Content

Some thoughts about:

  • Usually you'll need to work only with current version of your row, so your queries will not take into account previous versions while you're joining data, etc. On long term premise, this could have a huge impact on performance, statistics will not be accurate, data selectivity can negatively impact index selection, etc.
  • In case you work often with current values and historical value, you can define a view as a union on both 2 tables.
  • How to manage adding a new version? Within a transaction, copy the values from Entity in EntityHistory (by increasing version), then update Entity row with new values. Or alternatively, you could define a trigger on Entity table that will do trick behind.
  • Use a rowversion column: http://technet.microsoft.com/en-us/library/ms182776(v=sql.105).aspx

Upvotes: 1

Erwin Smout
Erwin Smout

Reputation: 18408

Just leave out the MasterTable.

Rows in your DetailsTable will still be "linked together", as you call it, by having the same ID column value.

Any other kind of useful "linking" you might want to do (e.g. link a row to its immediate successor or predecessor) is not achieved by having that MasterTable anyway. It achieves nothing (Unless you would want to have ID's in it, for which there is no Details, such that the ID never has been created, which seems rather unlikely). Leave it out.

Upvotes: 1

Related Questions