Is it correct way having status or Summary columns in parent table or should choose another precedure?

Question

My Parent Table is "Jobs" table, and there is child table that records Job's actions named "JobActions" as below:

Jobs (JobId, JobType, CurrentStage, AssignedTo)

JobActions (JobActionId, JobId, CreateDate, ActionType, FromUserId, ToUserId, Message, Detail)

in my grid in web page I list grid columns as below:

JobId, CreateDate, CurrentStage, AssignedTo, LastActionType, LastActionDate,...

In grid for calculating below columns I use joins to child table (very slow when there is many users and many data)

CreateDate ==> First(or min) JobActions' CreateDate,

LastActionType ==> Last JobActions' ActionType,

LastActionDate ==> Last(or Max) JobActions' CreateDate

I have about five columns like this that needs many join to JobActions or Another tables, So I Decided to change Job table like below:

Jobs (JobId, JobType, CurrentStage, AssignedTo, CreateDate, LastActionType, LastActionDate)

the last three columns always updated whenever any jobaction added to JobActions.

• Question 1: Is this a correct version, (am I denormalized my tables?)
• Question 2: Should I use trigger for this type of jobs or should update from inside my code?
• Question 3: Is this best practice? is there any better solution for this type of problems.

Answer 1

Question 3: Is this best practice? is there any better solution for this type of problems.

"Best Practice" might be dependent on the environment. The recommendation would be to try and figure out why a VIEW is "slow". If you can't make it faster, then your solution(s) seem to be fine (after considering the other Business Requirements).

Question 1: Is this a correct version, (am I denormalized my tables?)

In your original design, the JobActions table would be a Slowly Changing Dimension (SCD) Type 2 type of data.

In your other design, the additional columns (in the Jobs table) would be akin to SCD Type 4 type of data.

Your model looks fine (to me).

Question 2: Should I use trigger for this type of jobs or should update from inside my code?

Let's start with "should the update be placed inside your Application Code"? IMO - no. All applications should follow the same process when working with the same set of data. The only way to ensure that is to have the update logic within the database.

Should it be in a trigger? This answer may be highly opinionated.

On one hand, it could be easy to implement the required update logic in the trigger. On the other hand, implementing the delete logic might be difficult/impossible.

Alternative

Place the code in a Stored Procedure.

That is: Follow the Model-View-Control (MVC) philosophy

• hide your data Model from the application
• the application fetches the data from a View
• all DML manipulation is Controlled via Stored Procedures

In your example, you don't really need to create a VIEW on the tables (thus hiding your data Model). You just need to prevent (through GRANT/REVOKE) the application from performing DML outside of the Stored Procedures.