0

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.

  • What I have seen done in the past is a multi table structure. One table for the tables being audited, another for the individual columns being audited in each audited table and a final one that is a record per colum/row/table that is updated. This way you only store records that change and you can ignore columns that don't need to be audited. – Joe W Apr 22 at 20:11
  • Somebody might find of interest this Q & A. – MDCCL Apr 23 at 0:17
  • 1
    Do the math -- How big (gigabytes) will the Audit table become? If it is big, but affordable, will it be manageable? – Rick James Apr 28 at 15:01
  • 1
    Define "really fast". Elaborate on how many columns, how big, etc. Explain what type of query you need on the history -- "previous value", "all values throughout time", "plot this column over time", etc. Will you eventually purge "old" history rows? – Rick James Apr 28 at 15:04
0

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.

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.