5 ms·

All aggregating and all math I do in the db. Stuff like taxes, royalties, any reporting stuff (that should probably be in a column store). It may seem like a

All aggregating and all math I do in the db. Stuff like taxes, royalties, any reporting stuff (that should probably be in a column store). It may seem like a no-brainer to do that but you'd be surprised. I worked at a fintech briefly and ALL the math was done in Elixir which surprised me. I also usually like to do permissions in SQL, ie, selecting a record based on a permission as opposed to grabbing the record then checking (I go back and forth on this). I do like to have everything that is going on represented in the application code, though, so I've never actually written a trigger in my life... I did write a stored procedure once for generating unique product codes... I never said I was a particularly good engineer, lol. But ya, even in the case of a cascading delete or something I still like to spell that out in application code so there are no surprises. Of course, I've never gotten to work on a team where everyone felt this way. Audit trails seems like an interesting case I have thought about before (I have been a part of building audit trails at the app level before). It's something I've been curious about but never looked into. I think it depends on how detailed they have to be. Like, just creating a history of every table seems a little wasteful to me, but you're probably talking more nuanced than that. I did talk to someone who worked somewhere where that was the strategy.

JohnBooty 3y ago
I feel like audit trails are under-discussed and under-rated. Great for debugging (obviously, not as the sole debugging information, hopefully) and indispensible for covering your ass at times. If people screw up and put bad data in they frequently will blame the app and as a developer the burden of truth is often on you. Especially if the app has had bugs in the past... and what app hasn't? Like, just creating a history of every table seems a little wasteful to me, but you're probably talking more nuanced than that Yeah, doing it indiscriminately is wasteful to the extreme, no arguments! You would want to be judicious about the tables for which you employ it, the size of the data, frequency of updates, etc. In almost all cases you will surely want some kind of automated pruning ability. (Of course, this all applies at the app level as well, no difference here) What's unique about triggers is that they have access to the "old" and "new" versions of a row. Implementation varies but generally it's something like this Postgres example - note the special `OLD` and `NEW` tables. CREATE TRIGGER check_update BEFORE UPDATE ON accounts FOR EACH ROW WHEN (OLD.balance IS DISTINCT FROM NEW.balance) EXECUTE FUNCTION check_account_update(); Now, that specific is obviously "application" or "business" logic and it can be debatable if that should go into the database. But audit trails are more of a pure data layer concern IMO.
JohnBooty 3y ago
BTW, thank you for your reply and examples.

JohnBooty 3y ago

I feel like audit trails are under-discussed and under-rated. Great for debugging (obviously, not as the sole debugging information, hopefully) and indispensible for covering your ass at times. If people screw up and put bad data in they frequently will blame the app and as a developer the burden of truth is often on you. Especially if the app has had bugs in the past... and what app hasn't? Like, just creating a history of every table seems a little wasteful to me, but you're probably talking more nuanced than that Yeah, doing it indiscriminately is wasteful to the extreme, no arguments! You would want to be judicious about the tables for which you employ it, the size of the data, frequency of updates, etc. In almost all cases you will surely want some kind of automated pruning ability. (Of course, this all applies at the app level as well, no difference here) What's unique about triggers is that they have access to the "old" and "new" versions of a row. Implementation varies but generally it's something like this Postgres example - note the special `OLD` and `NEW` tables. CREATE TRIGGER check_update BEFORE UPDATE ON accounts FOR EACH ROW WHEN (OLD.balance IS DISTINCT FROM NEW.balance) EXECUTE FUNCTION check_account_update(); Now, that specific is obviously "application" or "business" logic and it can be debatable if that should go into the database. But audit trails are more of a pure data layer concern IMO.

JohnBooty 3y ago

BTW, thank you for your reply and examples.