Mysql – Using views with no insert table VS Audit tables

auditdatabase-designMySQL

I(not a DBA) am considering designing Audit tables for an application that needs to be really fast as well fully audited (undo is not necessary). I referred to some from so and I found variations of the below

  1. Create an insert only table with _to and _from and create a
    view from these.
  2. Create an Insert only table with just last_updated.
  3. Create a separate Audit table with both old and new values
  4. Create a separate Audit header table and Audit Detail table

I am choosing the following option and reasoning:

3 . Separate table but a single per table audit table – and just have created_by, created_date, updated_by and updated_date columns

  1. because I think insert only table would create too many records and might slow down quickly considering it will be a hotspot data for my application.
  2. Views will still be slower since they are just stored queries?
  3. Separate Audit table would help be separate functionality and feels right.
  4. Separate Header/Details is too much of an overhead

My question is considering the characteristics of application please provide if this makes sense.

Best Answer

what database is this, make sure to check your DB if it has some inbuilt native solution. But still if you want to develop Audit part do consider

  1. Insert and Select are difference and independent in relational databases.
  2. Try to trigger only the important columns you want to observer / monitor.
  3. Make sure you have a purge algorithm ready for stale data to keep the tables size in check.
  4. Make sure you properly partition the tables so that the selects are always efficient.
Related Question