My main table is the "Jobs" table, and there is a child table that records Job's actions called "JobActions" as follows:
Jobs (Job identification, JobType, CurrentStage, AssignedTo)
JobActions (JobActionId, Job identification, CreateDate, ActionType, FromUserId, ToUserId, Message, Detail)
In my grid on the webpage I list the grid columns as follows:
JobId, CreateDate, CurrentStage, AssignedTo, LastActionType, LastActionDate, …
In the grid to calculate the columns below, I use joins to child table (very slow when there are lots of users and lots of data)
CreateDate ==> First (or minimum) JobActions & # 39; CreateDate,
LastActionType ==> ActionType of Last JobActions,
LastActionDate ==> Last (or maximum) JobActions & # 39; CreateDate
I have around five columns like this that need to join either the JobActions or Another tables, so I decided to change the Job table as shown below:
Jobs (Job identification, JobType, CurrentStage, AssignedTo, CreateDate, LastActionType, LastActionDate)
the last three columns are always updated every time a jobaction is added to JobActions.
- Question 1: Is this a correct version (am I denormalizing my tables?)
- Question 2: Should I use the trigger for this type of work or should I update from my code?
- Question 3: Is this the best practice? Is there a better solution for this type of problem?