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)
in 7-10 tables or
2) a dedicated table with
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?