0

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:

PROJECTS

  • [PK] project_id
  • etc.

JOB

  • [PK] job_id
  • [FK] project_id
  • job_run_date
  • etc.

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)?

2
  • Yes, storing data that can be "inferred" or calculated in some way goes against normalisation. It also means you could have a discrepancy where one field says one thing but the calculation gives you another. Don't optimise prematurely. – Colin 't Hart Jun 5 '20 at 8:57
  • Right now this is just asking for a rewrite of a textbook with a bespoke tutorial & it shows no research effort. How to Ask Moreover as a question either re how to normalize or re basic design patterns it's a faq. – philipxy Jun 9 '20 at 19:25
1

Storing an attribute derived from a different table does introduce some risk of inconsistent data, if you fail to update projects after updating jobs for some reason, so you need to develop means to maintain integrity, e.g. by adding a trigger to jobs.

It does not necessarily goes against normalization principles per se, because you can model the project entity to include the last_used attribute and then your projects table remains in the same normal form as it was before.

Storing a bit of redundant data is a valid optimization technique when warranted. Computed columns, materialized views etc. fall in that category. If you exhaust other ways to make your application perform according to the specifications, you can use denormalization, knowing and mitigating its risks.

1
  • Yes. In this model the last_used job is the job with the latest run_date is a "business rule" not enforced by the database. For instance the business rule might state that the last_used job is the job with the latest run_date that ran to completion, etc. So the question is really whether the database should enforce every business rule it can, which is not a question of normalization at all. – David Browne - Microsoft Jun 4 '20 at 21:03
0

Full normalization implies that each fact is stored once and only once in the database. Materialized joins involve storing derived facts in the database, alongside the original facts.

This can occasionally be good design, especially in repoting databases or data warehouses. You do have to be careful during updates not to leave the database in an inconsistent state.

Sometimes, it's better to normalize, and create indexes that with reduce the cost of joins.

0

The mechanics of table normalization talk only about that table's candidate keys and the functional dependency of non-key columns on those keys. It has nothing to say about how one table relates to other tables in the same database.

The purpose of normalization, however, is to remove write anomalies from the model. If one fact changes in real life there should be a corresponding change to one column of one row in the database.

For your case putting a summary in a parent table respects the mechanics of normalization but breaks the purpose of it. And that's fine. As long as you document this, teach programmers and analysts about it and adequately enforce it (triggers, procedures, code reviews etc.) then introducing optimizations is legitimate.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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