How to version control a record in a database [closed]

by

Let’s say you have a FOO table that admins and users can update. Most of the time you can write queries against the FOO table. Happy days.

Then, I would create a FOO_HISTORY table. This has all the columns of the FOO table. The primary key is the same as FOO plus a RevisionNumber column. There is a foreign key from FOO_HISTORY to FOO. You might also add columns related to the revision such as the UserId and RevisionDate. Populate the RevisionNumbers in an ever-increasing fashion across all the *_HISTORY tables (i.e. from an Oracle sequence or equivalent). Do not rely on there only being one change in a second (i.e. do not put RevisionDate into the primary key).

Now, every time you update FOO, just before you do the update you insert the old values into FOO_HISTORY. You do this at some fundamental level in your design so that programmers can’t accidentally miss this step.

If you want to delete a row from FOO you have some choices. Either cascade and delete all the history, or perform a logical delete by flagging FOO as deleted.

This solution is good when you are largely interested in the current values and only occasionally in the history. If you always need the history then you can put effective start and end dates and keep all the records in FOO itself. Every query then needs to check those dates.

More Related Contents:

  1. Versioning Database Persisted Objects, How would you? [closed]
  2. Should I use a single or multiple database setup for a multi-client application? [closed]
  3. How to Store Historical Data [closed]
  4. How to design a product table for many kinds of product where each product has many parameters
  5. What is Normalisation (or Normalization)? [closed]
  6. Database design – articles, blog posts, photos, stories
  7. Schema for a multilanguage database
  8. Calendar Recurring/Repeating Events – Best Storage Method
  9. designing database to hold different metadata information
  10. Same data from different entities in Database – Best Practice – Phone numbers example
  11. Sql – Indirect Foreign Key
  12. Database Design for Revisions?
  13. What are the lengths of Location Coordinates, latitude and longitude? [closed]
  14. Dynamic Database Schema [closed]
  15. How should I implement this schema in MongoDB?
  16. Why use multiple columns as primary keys (composite primary key)
  17. Microservices with shared database? using multiple ORM’s? [closed]
  18. How do you deal with polymorphism in a database?
  19. Nullable Foreign Key bad practice?
  20. How do I design a database to store properties, selecting attributes by synonyms
  21. Composite primary key or not?
  22. Can we create multicolumn unique indexes on MS access databases?
  23. Should I have a dedicated primary key field?
  24. Database design for audit logging [closed]
  25. Database Design Best Practices [closed]
  26. A column as primary key or two foreign keys as primary key
  27. Bill of materials database model
  28. Databse architecture (single db vs client specific db) for Building Enterprise Web (RIA) application on cloud
  29. What are design patterns to support custom fields in an application?
  30. mongodb schema design for blogs

Leave a Comment