Is there any reason to maintain audit columns when the target table already has an audit table? I'm currently designing a database schema for a small microservice which handles products.
The service contains a simple REST API which allows users to perform basic management of products and also some end points to query the full audit history about products. Schema already contains a snapshot-based audit table for products to support storing the full audit trail per product.
In addition of having separate audit history end points, each product specific REST API response must also include the createdAt, createdBy, updatedAt, updatedBy fields. The thing I'm wondering here is whether I should add these fields into products table or is it better to query information to those fields directly from the product audit table? Somehow I feel like adding these fields into the products table would seem a bit overkill when the same data (and even more) could be queried from the audit table.
Edit
Each CREATE, UPDATE and DELETE operation on the product table will also append a new entry into the audit table. This audit table contains the same columns than the actual product table plus additional columns for the timestamp, user ID and the type (ADD, MOD, DEL) of the audit event. The frequency of the management operations is quite low.
If you are going to follow the pattern of inserting into the audit table after each insert, no there is no need to retain the creation/alter timestamps as they are present in the audit table.
However, if the pattern is to only insert into the audit table after an update/delete (this is more common), then you will need to retain any relevant timestamps/user info with the data so the original data can be copied into the audit table.
Generally, unless there is a very specific use case, it isn't necessary to store the created at/created by information as this is easily derived from the audit information itself.