CODEWITHSUNDEEP
entity-frameworkdatabase-designorm
DCNYAM
DCNYAM

Reputation: 12126

Where to place auditing fields?

In our shop, when we design a database, we typically include auditing attributes for each table (LastUpdateUser, LastUpdateDate, etc). This is common practice, however, I've noticed this becoming an increasing problem when you have tables that "inherit" from other tables, especially using tools such as the entity framework.

For example, if you have tables Customers and Employees, and those tables have a foreign key to table People, then in your entity / class model when you establish the inheritance, you need to change the names for the audit fields because they exist in both tables. Perhaps they need to become PersonLastUpdatedUser and PersonLastUpdatedDate, while the ones from Employees remain as simply LastUpdatedUser and LastUpdatedDate.

When designing tables for inheritance, do you put such audit fields in both tables, or do you just have them in the parent table and update the parent table whenever an attribute changes in a child table?

Upvotes: 0

Views: 248

Answers (2)

HLGEM
HLGEM

Reputation: 96552

If you want true auditing, you create separate audit tables that are populated by triggers (never ever by the application or you will miss items that need to be audited). and they shouw both the old and new value as well as the date and the user or application that made the change.

If you want a last updatedcolumn in each table (which I think is better than having it only in the parenta as that doesn't tell you anything about which of the tables changes last) and you want o use inheritance then you might need to create unique names by adding the table name to lastUpdated. So PersonLastUpdated and OrderLastUpdated, etc.

Or you don't use inheritance.

Upvotes: 0

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364269

If you want to use inheritance than those attributes belong to parent table because the parent with related table forms single entity and you track auditing for whole entity. If you for any reason needs those attributes in both tables it should be the first warning that those tables are not good candidates for inheritance.

Upvotes: 1

Related Questions