To simplify lets say I have two tables PROJECTS and JOBS. A user creates a project and runs 1 or more jobs using a project.
So it would possible look something like this:
- [PK] project_id
- [PK] job_id
- [FK] project_id
Ideally I want to be able to list all projects by last used (last job run) and display them to the user. When I create a job, I can insert the job then update the project entry to have a
last used; or I can just have it insert the job, then when I'm displaying all projects, I can do a join and aggregation with JOBS to get the last used date.
I figure, the drawback of the join is that as you have more jobs, performance may be slowly impacted over time to do the joins and aggregations. Whereas, being somewhat redundant increases the chance of a transaction failure and wastes a bit of space.
If I were to choose the update both tables (i.e. have a last_used` column in PROJECTS), would that be going against normalization (or good db design practices)?