Common Audit Table for all Entities

Need an opinion

Do you think its a good idea storing basic audit information for all entities in a common database table? I mean Created By, Created On, Modified By, Modified On. The entity table will have the Audit Id & timestamp

Audit information is typically queried during updates to check conflicts and sometimes from business logic associated with the Creator for e.g. owner of the record in a CRM New Lead row.

May be we should never use audit fields as business fields.

I think it will make the inserts slow because of two tables per entity but overall will make the entity classes compact and will make auditing a O开发者_开发技巧N/OFF switch

There are basically three options I'm aware of:

1. Common audit table for all entities as you suggested.
2. One audit table per entity, having all (or a subset) of that entity's fields, plus additional information such as the user and timestamp as it relates to the entity being saved at that point in time.
3. Each entity table can have multiple versions of the same entity. You'd then have a view for each entity table to select out the most recent version of the entity.

It really depends on what sort of audit data you're interested in. In many cases, it's no use knowing who made a change and when if you can't see what the change actually was because other people have changed the same entity since then.

For security reasons, audit data should be separated from entity data. This way, if a hacker gains access to your application, he may be able to change data, but he will not be able to change the audit information about what he did.

Separating audit data into its own schema is one approach. You could also use an entirely separate database. In either case, you want to be sure you have 2 user accounts: one that only has access to your entity data and another that only has access to the audit data.

I often see some type of publish/subscribe model used to publish audit data and then asynchronously write that audit data to the audit store.