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.