Designing a History Table for Auditing and Data Tracking

Designing a History Table for Auditing Changes

Write a SQL query to design a history table for auditing changes to a main table.

Solution:

-- Main table.
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    Salary DECIMAL(10, 2)
);

-- History table for tracking changes.
CREATE TABLE EmployeeHistory (
    HistoryID INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeID INT,
    Name VARCHAR(100),
    Salary DECIMAL(10, 2),
    ChangeDate DATETIME DEFAULT GETDATE()
);

Explanation:

• Purpose of the Query :
• The goal is to design a history table to track changes made to the main table.
• Key Components :
• EmployeeHistory: Stores historical records of changes to employee data.
• Includes a timestamp (ChangeDate) to record when changes occurred.
• Why Use History Tables? :
• History tables provide an audit trail for tracking updates, deletions, or insertions.
• They are essential for compliance, debugging, and analysis.
• Real-World Application :
• In financial systems, history tables track salary adjustments or role changes.

Notes:

• History tables increase storage requirements but provide valuable insights.
• Use triggers or application logic to populate history tables automatically.
• Important Considerations:
• Regularly archive old records to manage storage growth.

For more Practice: Solve these Related Problems:

• Write a SQL query to design a history table for tracking changes to customer contact information.
• Write a SQL query to create a history table for auditing updates to product prices in an inventory system.
• Write a SQL query to design a history table for logging changes to employee job titles and departments.
• Write a SQL query to create a history table for tracking modifications to supplier contracts in a procurement system.

Go to:

• Database Design and Normalization Exercises Home ↩
• SQL Exercises Home ↩

PREV : Designing a Lookup Table for Categorical Data.
NEXT : Designing a Partitioned Table for Large Datasets.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.