2

By no means am I a DB expert, but I do have experience with creating DBs. Not sure about the following though.

I want everything that happens in the system to be trackable. My client is dealing with lots of money and if mistakes are made, they need to be tracked. From both a DB and a developer perspective, what's easier: 1)

  • created_by
  • created_date
  • updated_by
  • updated_date

in 7-10 tables or

2) a dedicated table with

  • action_done
  • action_done_by
  • action_date

and then a secondary table with what the actions are. id action_type

Pros to 1: Easier to make sure you don't miss anything. Cons to 1: Annoying to maintain, report on, etc. and so not clean.

Pros to 2: Oh so clean, easy to report on. Cons to 2: Easier to miss something you want to log (though that's possibly true for 1, it's less likely because the fields are in each table and they need to be used for something.)

Is that it and I just need to make a decision one way or the other? Or is there a DB performance concern I should have or something else I'm missing?

3
  • 1
    You should probably wait a day or two before accepting an answer; there are folks in different time zones that may not have had a chance to post a better response. – mustaccio Dec 13 '19 at 19:41
  • If a better answer comes along, you can always change the accepted answer. – Paul White Dec 14 '19 at 6:32
  • Oops, sorry, good to know, thanks! Your answer seemed to address everything I was looking for plus, so figured it was good to go. I'll keep that in mind for the future (and the next questions I'm about to post lol) – EAJ Dec 15 '19 at 18:30
1

Your two scenarios serve different purposes. The former is useful when you application needs to know the history of an entity, e.g. to display the balance sheet as of last December. See also "slowly changing dimensions" and "temporal tables".

The latter is closer to the true audit trail, which should be separate from the data it reports on, and probably even from the database it reports on -- otherwise that same [compromised] application that enters erroneous data can also modify the audit trail to disguise the change. Storing audit data separately from its source entities also benefits performance -- the application doesn't need to sift through days/months/years of historical data to get at the current records.

If you decide to store the audit trail in the database, at least ensure that the authorization ID that modifies the data cannot access the audit trail table, and the audit authorization ID cannot change the source data (also known as separation of duties).

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.