Database Design – Should Audit Columns Be Maintained with an Audit Table?

auditdatabase-design

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.

Best Answer

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.

Model

Here's a possible model. After I drew it I noticed that the difference with yours is that the product part number is now in the Product table; I think it should be this way as it's a characteristic of the product. Then the Job refers to a Product via its part number, and not viceversa.

Each table uses a surrogate primary key for clarity. This is not really necessary in several cases e.g. in RepairComponent, so feel free to replace it with a composite primary key.

The Product table stores a product. The field Product.pn (part number) should identify your product since it is manufactured; the product is then assigned a DMR when it fails and then, if it is repaired, a S/N.

Product
------------------
pn (PK)
DMR (IDX unique)
SN

Job table:

Job
------------------
id (PK)
Product.pn (FK)
due_date
quantity_to_ship

A product can fail multiple times and can be repaired multiple times (many to many relationship).

A product may fail multiple times but each repair concerns only one product, so it's a one-to-many relationship. You can hence have simply one table Repair (that stores a repair event) referring to Product.

Failure
------------------
id (PK)
Product.DMR (FK)
description
operation
date
Technician.id (FK)

Repair
------------------
id (PK)
Product.DMR (FK)
disposition
code
date
Technician.id (FK)

RepairComponent
------------------
id (PK)
component_pn
Repair.id (FK)

Technician
------------------
id (PK)
name

Enum vs table

I'm a bit unsure when to use an enum vs creating another table (e.g. disposition can be scrap, repaired, or use-as-is). Am I on the right track?

If you are dealing with an immutable set of operations (i.e. you're absolutely positive that Repair.disposition will always be one and only one of the options you listed) then it's ok to use an enum. Otherwise, your ER model will be much more flexible if you create another table:

DispositionTypes
------------------
id (PK)
description

as now you can create all kinds of new disposition types e.g. "Repaired", "Use as is", "Scrap", "Sell to China", or "Bury in landfill". In this case, you remove Repair.disposition and replace it with a FK DispositionTypes.id instead.

The advantage of this approach is that you can set up authorization levels for Technicians to perform a specific disposal (it is not mentioned in your question but it shows in your model, if I'm not wrong). This is done via a join table Authorizations:

Authorization
------------------
Technician.id      (PK) (FK)      
DispositionType.id (PK) (FK)

Same talk for RepairCode:

RepairCode
------------------
id (PK)
description

Personally I like to design databases that allow for flexibility, but the choice here it's up to you.

Choice of the DB engine

A MySQL database with InnoDB (which is the standard engine in MySQL) will work fine, so it's a good choice.

MySQL Database Design – How to Design a Website Database Structure

What you could do is a design like this:

Product(id,name,description,price,productType)
ProductAttribute(productId,attributeName,value)

Then you would have the possibility to filter for all attributes that are currently used in your database.

If you want to be able to filter for all attributes, even if they are not used in any product at the moment, you'll have to add a table

AttributesForProductType(productType,attributeName)

which contains all possible attributes, and then set up referential integrity (foreign keys) between AttributesForProductType and ProductAttribute. This way you lose the risk of misspelling an attribute name in the ProductAttribute table.

edit: If you take this design this also solves your second question: How to display a details page for every possible product type?

All possible attributes for this product are

select attributeName
from AttributesForProductType
where productType=?

all real attributes for a product with their values are

select attributeName, value
from ProductAttribute
where id=?

and those combined are (off of my head, untested)

select apt.attributeName, pa.value
from Product as p
left outer join AttributesForProductType as apt on p.productType=apt.ProductType
left outer join ProductAttribute as pa on apt.attributeName=pa.attributeName
where p.id=?

Best Answer

Related Solutions

MySQL Database Design – Potential Schema for Product Failures and Repairs

Model

Enum vs table

Choice of the DB engine

MySQL Database Design – How to Design a Website Database Structure

Related Question