The Best Way To Add Audit Tables to Your Database

Summary

This video explains three methods for adding audit tables or change logs to a database: 'live history' (row versioning), 'shadow history,' and 'generic audit table.' 'Live history' keeps all versions of records within the same table, while 'shadow history' separates live and historical data into different tables, improving performance. The 'generic audit table' uses a single table to record changes across the database, offering flexibility but increasing complexity. The video concludes by recommending 'shadow history' for its balance between ease of use and performance, despite the need for extra tables.

Takeaways

• 😀 The first audit table option is 'Live History' or 'Row Versioning', where changes are added as new rows, with a version number and timestamp.
• 😀 In 'Live History', a username field captures the user who made the change, and an 'is_active' column helps manage soft deletes.
• 😀 'Live History' can lead to complex queries, especially when retrieving the latest data, and requires using multiple-column primary keys.
• 😀 The second option is 'Shadow History', where a separate audit table stores historical changes while keeping live data in the main table.
• 😀 With 'Shadow History', changes made to the main table are recorded in an audit table with fields like 'username' and 'update_datetime'.
• 😀 One advantage of 'Shadow History' is that it maintains the performance of live queries by separating historical data into another table.
• 😀 'Shadow History' requires combining the main table and the audit table in queries, which may add complexity when viewing data history.
• 😀 The third option is the 'Generic Audit Table', which logs changes for all tables in the database into one central audit table, capturing detailed change information.
• 😀 'Generic Audit Table' requires handling various data types and may require large VARCHAR fields for old and new values, making it more complex to manage.
• 😀 The speaker recommends 'Shadow History' as the best option due to its balance between performance, simplicity, and effective tracking of data changes over time.
• 😀 While all three options require code to manage auditing, 'Shadow History' is preferred for reducing schema clutter and improving maintainability over time.

Q & A

What are the three ways to add audit tables or change logs to a database?

The three ways to add audit tables or change logs are: 1) Live History (Row Versioning), 2) Shadow History, and 3) Generic Audit Table.

What is the Live History (Row Versioning) approach for auditing?

In the Live History approach, any change made to a row is added as a new record in the same table, along with additional fields such as a username, version_datetime, and a version_number that increments with each change.

What is a soft delete, and how is it used in the Live History approach?

A soft delete is a technique where a row is not physically deleted but marked as inactive. In the Live History approach, an 'is_active' column is used to indicate whether a row is active (1) or deleted (0).

What is the main disadvantage of the Live History approach?

The main disadvantage of the Live History approach is the complexity of queries required to fetch the latest version of records and exclude inactive rows, which can affect performance, especially with large tables.

What is the Shadow History approach for auditing?

In the Shadow History approach, a separate audit table is used to store historic data. Updates and deletions in the main table are logged in the audit table, while the main table only contains live records.

What are the advantages of the Shadow History approach?

The advantages of Shadow History include better performance when selecting live data, as the historic data is in a separate table, and easier management of historical records since they are stored independently.

What are the disadvantages of the Shadow History approach?

The disadvantages of Shadow History include the need to combine two tables in a query to view the change history, and the additional work required to update the audit table structure when the main table structure changes.

What is the Generic Audit Table approach for auditing?

The Generic Audit Table approach uses a single audit table for all auditable tables in the database. This table records changes by capturing details such as table name, field name, old_value, new_value, username, and update_datetime.

What are the advantages and disadvantages of the Generic Audit Table approach?

The main advantage of the Generic Audit Table approach is having a single table for all audit data, which simplifies the schema. The disadvantages include the need to handle various data types in the audit table and difficulty in viewing record history or recreating records at a specific point in time.

Which audit table approach is recommended in the video, and why?

The video recommends the Shadow History approach, as it balances simplicity with performance. It allows easy tracking of data changes over time while maintaining good performance for live data queries.