Best design for a changelog / auditing database table? [closed]

by

In the project I’m working on, audit log also started from the very minimalistic design, like the one you described:

event ID
event date/time
event type
user ID
description

The idea was the same: to keep things simple.

However, it quickly became obvious that this minimalistic design was not sufficient. The typical audit was boiling down to questions like this: 

Who the heck created/updated/deleted a record 
with ID=X in the table Foo and when?

So, in order to be able to answer such questions quickly (using SQL), we ended up having two additional columns in the audit table

object type (or table name)
object ID

That’s when design of our audit log really stabilized (for a few years now).

Of course, the last “improvement” would work only for tables that had surrogate keys. But guess what? All our tables that are worth auditing do have such a key!

More Related Contents:

  1. Ideas on database design for capturing audit trails [closed]
  2. Is storing a delimited list in a database column really that bad?
  3. What’s the difference between identifying and non-identifying relationships?
  4. Can I have multiple primary keys in a single table?
  5. How are super- and subtype relationships in ER diagrams represented as tables?
  6. What are database normal forms and can you give examples? [closed]
  7. Difference between scaling horizontally and vertically for databases [closed]
  8. NoSql vs Relational database
  9. Multiple schemas versus enormous tables [closed]
  10. Best practices for storing postal addresses in a database (RDBMS)?
  11. What is the most efficient way to store tags in a database?
  12. Microservices with shared database? using multiple ORM’s? [closed]
  13. Why use a 1-to-1 relationship in database design?
  14. What is the optimal length for an email address in a database?
  15. What are the advantages of using a single database for EACH client?
  16. Database optimization orders
  17. In what way does denormalization improve database performance?
  18. Still Confused About Identifying vs. Non-Identifying Relationships
  19. Alternatives to Entity-Attribute-Value (EAV)?
  20. have address columns in each table or an address table that is referenced by the other tables?
  21. Minimum no of tables that exists after decomposing relation R into 1NF?
  22. Is it better to use multiple databases with one schema each, or one database with multiple schemas? [closed]
  23. Is it a good idea to use an integer column for storing US ZIP codes in a database?
  24. Limit an sqlite Table’s Maximum Number of Rows
  25. What’s the better database design: more tables or more columns?
  26. Should OLAP databases be denormalized for read performance? [closed]
  27. Best beginner resources for understanding the EAV database model? [closed]
  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