Database – Data Versioning [closed]

by

I have done various audit schemes over the years and I am currently going to implement something like this:

Person
------------------------------------------------
ID                UINT NOT NULL,
PersonID          UINT NOT NULL,
Name              VARCHAR(200) NOT NULL,
DOB               DATE NOT NULL,
Email             VARCHAR(100) NOT NULL


Person_History
------------------------------------------------
ID                UINT NOT NULL,
PersonID          UINT NOT NULL,
Name              VARCHAR(200) NOT NULL,
DOB               DATE NOT NULL,
Email             VARCHAR(100) NOT NULL
AuditID           UINT NOT NULL


Audit
------------------------------------------------
ID                UINT NOT NULL,
UserID            UINT NOT NULL,               -- Who
AffectedOn        DATE NOT NULL,               -- When
Comment           VARCHAR(500) NOT NULL        -- Why

The current records are always in the Person table. If there is a change an audit record is created and the old record is copied into the Person_History table (note the ID does not change and there can be multiple versions)

The Audit ID is in the *_History tables so you can link multiple record changes to one audit record if you like.

EDIT:
If you don’t have a separate history table for each base table and want to use the same table to hold old and “deleted” records then you have to mark the records with a status flag. The problem with that it’s a real pain when querying for current records – trust me I’ve done that.

More Related Contents:

  1. Versioning Database Persisted Objects, How would you? [closed]
  2. Should each and every table have a primary key?
  3. Database development mistakes made by application developers [closed]
  4. What’s wrong with foreign keys?
  5. Surrogate vs. natural/business keys [closed]
  6. What are database normal forms and can you give examples? [closed]
  7. What is the best design for a database table that can be owned by two different resources, and therefore needs two different foreign keys? [closed]
  8. NoSql vs Relational database
  9. Multiple schemas versus enormous tables [closed]
  10. First name, middle name, last name. Why not Full Name?
  11. What are the design criteria for primary keys?
  12. Techniques for database inheritance?
  13. What is the most efficient way to store tags in a database?
  14. Microservices with shared database? using multiple ORM’s? [closed]
  15. Supertype-subtype database design
  16. In terms of databases, is “Normalize for correctness, denormalize for performance” a right mantra?
  17. How should international geographical addresses be stored in a relational database?
  18. Still Confused About Identifying vs. Non-Identifying Relationships
  19. Which is a good design for a database table that can be owned by two different resources, and therefore needs two different foreign keys? [closed]
  20. What’s wrong with nullable columns in composite primary keys?
  21. Best representation of an ordered list in a database?
  22. Ideas on database design for capturing audit trails [closed]
  23. Is it better to use multiple databases with one schema each, or one database with multiple schemas? [closed]
  24. Is it a good idea to use an integer column for storing US ZIP codes in a database?
  25. Best design for a changelog / auditing database table? [closed]
  26. Best beginner resources for understanding the EAV database model? [closed]
  27. Databse architecture (single db vs client specific db) for Building Enterprise Web (RIA) application on cloud
  28. Derived account balance vs stored account balance for a simple bank account?
  29. In a StackOverflow clone, what relationship should a Comments table have to Questions and Answers?
  30. uncertainty in developing a database model

Leave a Comment