Back to articles list
It is important to log information about changes in a database. We should track who introduced the change, when it happened, and what was done. This way, even if the operational data does not exist anymore (for example, because the record has been deleted), we can still recover a trace of the user's activity.
How Should the Logs Be Stored?
First of all, logs should be stored in a separate table (or tables). Second, they must contain a comprehensive copy (not reference!) of the data. This applies to both the object on which the operation was performed (for example, an order) and the person who performed the operation (a user or a customer). Finally, we must log the timestamp.
In the example below, the table order_log contains a timestamp, the ID of the user that performed the operation, and information about the modified order and customer objects.
Optionally, the log tables may contain nullable references to other tables (order and customer in our example) that can facilitate further investigations. However, this does not change the fact that the key data should be stored as a copy.
Note that the log tables do not necessarily have to reflect the columns of the operational tables. It is often sufficient to store the collected information in a single text column (in our example, the information about the order is saved in order_info and information about the customer in customer_info). Here, we do not care about the performance so much, as advanced searches or calculations are usually not executed on log data.
