0

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.

1 Answer 1

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.

1
  • Thank you for your answer, You make me learn a new Concept, SCDs, thank you alot. I asked same question in stackoverflow in case I cannot get answer from DBAs, I will paste your answer there with your name.
    – mesut
    Apr 5, 2020 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.