sql触发器和技术债务[关闭]

Question

My boss asked me to create a easier system for finding points by having points associated with the user table in our mysql database. The old system just had events, there point values, then another table with events completed for a user, and then another table for just admin given points. So my job was to add these all together and put them in a column. Now he says the problem is that there is still all the queries running around adding points, but instead of changing them to simply add points to the users column upon task completion, they suggested i use a trigger to simply add points to the users column, when one of the other columns has points added to it.

To me this sounds like using a work-around and creating technical debt. Am i wrong?

Im new to the system, and i dont know exactly where all the queries are in the php pages, but if this is creating technical debt, what would be the appropriate way to fix this.

Im new and am probably going to just use sql triggers as to not go against my boss's suggestions, I want to at least know the smart/best way to do things.

Doing my best to provide not actual, but near actual db schema EVENT: ID, point value, Desc User-Events: USERID, EVENTID, COMPLETION-STATUS GIVEN-POINTS:USERID, POINTS_GIVEN, DESC (Each time points are given, so its more of a log than updated points)

I added a Points column to the basic USER TABLE the trigger would be when user-Event completion-status =done, find point value, add to points in user, instead of changing queries to do that.

Answer 1

Triggers are a perfectly valid way to accomplish what you are trying to do, as long as the business rules are fairly simple.

There are lots of ways to accomplish moving data from one table to another. You can use triggers, some sort of synchronous PHP process or an asynchronous process using some sort of message queue.

Triggers have the benefit of being simple and fast to code, maintain, and run. The upside is that you only have to do the code once, which is especially nice since you don't know where all the queries that touch these tables are. The downside is that you could be putting business logic into the database, which is where you might start getting into technical debt. The other downside is simply that you've added another business layer, which might not be obvious to the next developer, so they might spend a lot of time trying to figure out how and why the summary table is being updated. Comments are a good thing, in this case.

Synchronous PHP processes are are nice in that it's very obvious where the code is being executed. The other upside is that you have access to the whole PHP application context and can create more complex business rules. The downside is that you will have to put the function or method call into each place where the table is potentially being touched.

Asynchronous PHP processes have the same up and downsides as the synchronous PHP processes, with the added benefit that they aren't going to slow down the user experience. They are also a little more complex to create; you have to handle cases where the messages aren't received, or aren't received in the correct order.