2

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.

2
  • Can you share a small subset of data? The answer depends on how/when you are inserting data into the audit table.
    – bbaird
    Sep 21, 2020 at 12:34
  • @bbaird Added a small edit that describes when the audit table is formed and what kind of data it will contain. Unfortunately, I cannot give any subset of data atm.
    – idkfa
    Sep 21, 2020 at 14:13

2 Answers 2

1

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.

0

"Last updated" can be used to detect write conflicts, where a value is read, changed and written by different users concurrently. Having this value in the data table makes detection slightly simpler. A datetime value is not required for this use case, and can be problematic in certain corner cases.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.